I have a table in a MySQL 5.5 database with around 7 million records, with fields typeId, regionId, scanTime, scanId and a query that returns around 239,000 results.

For every combination of typeId and regionId, I want to find the most recent scanId/scanTime. (There are a small number of regionIds and a large number of typeIds).

I initially approached this with the following naive query:

SELECT max(scanTime), max(scanId), typeId, regionId 
FROM scanTimes 
GROUP BY typeId, regionId;

At this time there was the primary key index on scanId and an index on typeId, regionId, and the query took a few minutes as it had to read the entire database to calculate the aggregates.

As the scanId is auto-incrementing, and time shouldn't run backwards under normal conditions, carrying out an aggregate on both scanId and scanTime seemed a bit wasteful - it should surely be possible to just select the record containing the highest Id, then retrieve the matching Time.

So I adjusted my query to use a subquery as follows:

SELECT sq.maxScanId, q.scanTime, q.regionId, q.typeId 
  (SELECT max(scanId) as maxScanId, regionId, typeId 
  FROM scanTimes 
  GROUP BY regionId, typeId
  ) sq 
INNER JOIN scanTimes as q 
ON q.scanId = sq.maxScanId AND q.regionId = sq.regionId AND q.typeId=sq.typeId

I also added an index on typeId, regionId, scanId, and another on typeId, regionId, scanTime (though it turns out I don't need the latter).

This got the query down to a few seconds which is a big improvement. Is it possible to do any better than this?

This is the result of EXPLAIN:

| id | select_type | table              | type   | possible_keys                                               | key                | key_len | ref          | rows   | Extra                    |
|  1 | PRIMARY     | <derived2>         | ALL    | NULL                                                        | NULL               | NULL    | NULL         | 239307 |                          |
|  1 | PRIMARY     | q                  | eq_ref | PRIMARY,REGION_TYPE,SCANTIME_REGION_TYPE,region_type_scanid | PRIMARY            | 4       | sq.maxScanId |      1 | Using where              |
|  2 | DERIVED     | scanTimes          | range  | NULL                                                        | region_type_scanid | 8       | NULL         |    201 | Using index for group-by |

Is it possible to optimise this query further?


What database? Does your database support row_number().

And you changed the query. In the first max(scanTime), max(scanId) are not necessarily on the same row. Those are two independent max.

The scanTime of the row with the max(scanID) is different and what you got with the second. It is faster as scanID is indexed (PK) and scanTime is not. Since scanID is the PK you don't need:

AND q.regionId = sq.regionId AND q.typeId=sq.typeId

If your database supports row_number()

select * from 
SELECT scanTime, mscanId, typeId, regionId  
     , row_number over (partition by typeId, regionId order by mscanId desc) rn
FROM scanTimes 
) tt
where tt.rn = 1
  • DB is MySQL. max(scanTime) and max(scanId) should return the same record really (at least to the limits of time precision), as both should be increasing monotonically. I'll update the post with DB info. – frankster Nov 25 '15 at 16:48
  • So they should be the same record due to the nature of the data. Still, it is two independent max. – paparazzo Nov 25 '15 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.