I have a query that I have made into a MYSQL view. This particular view is central to our application so we are looking at tuning it. There is a primary key on Map_Id,User_No,X,Y. I am pretty comfortable tuning SQL server queries but not totally sure about how MySql works in this aspect. Would it help to put an index on it that covers points and update_stamp as well? Reads on this table are 90% so while it has lots of inserts, it does not compare to the amount of reads.
Description: Get the person with the most points for each x,y coord in a given map. Tie break by who has the latest update stamp and then by user id.
SELECT GP.Map_Id AS Map_Id,GP.User_No AS User_No,GP.X AS X,GP.Y AS Y, GP.Points AS Points,GP.Update_Stamp AS Update_Stamp FROM (Grid_Points GP LEFT JOIN Grid_Points GP2 ON ( ( (GP2.Map_Id = GP.Map_Id) AND (GP2.X = GP.X) AND (GP2.Y = GP.Y) AND ((GP2.Points > GP.Points) OR ((GP2.Points = GP.Points) AND (GP2.Update_Stamp > GP.Update_Stamp)) OR ((GP2.Points = GP.Points) AND (GP2.Update_Stamp = GP.Update_Stamp) AND (GP2.User_No < GP.User_No))) ) ) ) WHERE ISNULL(GP2.User_No);