mysql query tuning

Question

Hello, 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);

Answer 1

I wouldn't match it to itself, I'd do it as a "group by" and then possibly match back to get who the person is.

SELECT Map_Id, X, Y, max(Points) FROM Grid_Points GROUP BY Map_Id, X, Y;

This would give you a table of Map_Id, X, and Y, then the maximum points. You could then join those results back to Grid_Points to find which user is = to those points.

Answer 2

Wow man, you really like to use parentheses. :-)

You're right, a compound index may help. You might even be able to make it a covering index. Probably either an index on Grid_Points(Map_Id,X,Y) or else an index on Grid_Points(Points,Update_Stamp,User_No) would be what I try.

Always test query optimization with EXPLAIN to see if the optimizer is using your index. Read that documentation section until you understand the cryptic notes in the EXPLAIN report.

The EXPLAIN report will probably show you which index it decides to use. You should be aware that MySQL uses only one index per table in a given query.

Here's how I would write that query, relying on order of precedence between AND and OR instead of so many nested parentheses:

SELECT GP.Map_Id, GP.User_No, GP.X, GP.Y, GP.Points, GP.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 GP2.User_No IS NULL;

You are using my favorite method for finding greatest-n-per-group in MySQL. MySQL doesn't optimize GROUP BY very well (it often incurs a temporary table which gets serialized to disk), so the left outer join solution that you're using is usually a lot better, at least for MySQL. In other brands of RDBMS, this solution may not have such an advantage.