Search code examples
databasedatabase-designgroup-byquery-optimizationgroupwise-maximum

MySQL Query Optimization Group By with Max

Trying to optimise MySQL query which uses Group by with Max. Trying to get latest updated frames which were active before any past date.

Audit Frame Table Structure:

id bigint pk AI
frame_id bigint fk
name VARCHAR(50)
active int
createdOn DATETIME
updatedOn DATETIME
deletedOn DATETIME

id, frame_id and updatedOn have individual indexes..

Current Query:

SELECT MAX(id) as id1 
  FROM audit_frame_master 
 WHERE updatedOn <='2019-03-25 21:00:00' 
   AND active=1 
   AND deletedOn IS NULL 
 GROUP 
    BY frame_id

Performance:

Table has around 1M rows. It takes average 4 seconds to perform.. Is there any way to optimise above query?

Below is the EXPLAIN statement for the same. enter image description here

Solution

  • The query looks fine. All you can do is provide an appropriate index. That would be an index on the columns in the WHERE clause at least. Start with the most restrictive column. So,

    • how many rows match active = 1?
    • how many rows match deletedOn IS NULL?
    • how many rows match updatedOn <= timestamp '2019-03-25 21:00:00'?

    Pick the one that gets the least number of rows. Say it's active, then updatedOn, then deletedOn. This gives you:

    create index idx on audit_frame_master(active, updatedOn, deletedOn);

    As you want to group by frame_id then and then find the maximum id, you can add those in this order:

    create index idx on audit_frame_master(active, updatedOn, deletedOn, frame_id, id);

    This is a covering index. If the DBMS uses it, it doesn't even have to access the table.

    The DBMS may or may not use this index. It's just an offer. If the DBMS thinks it will be too much work to go through an index rather then simply read the table sequentially, then it won't use it. Just try.