I had problems with my request, which took 17 seconds to complete (lines 350 thousand):
SELECT idgps_unit, MAX(dt)
FROM gps_unit_location
GROUP BY 1
Explain
1 SIMPLE gps_unit_location index fk_gps2 5 422633
After playing with this, I came up with this solution, which takes 1 second:
Select idgps_unit, MAX(dt) from (
SELECT idgps_unit, dt
FROM gps_unit_location
) d1
Group by 1
I explain:
1 PRIMARY <derived2> ALL 423344 Using temporary; Using filesort
2 DERIVED gps_unit_location index gps_unit_location_dt_gpsid 10 422617 Using index
And now I'm confused: why query # 2 is fast, and query # 1 seems to be the same query and seems to be written more efficiently.
Index1: DT, Index2: idgps_unit, Index3: idgps_unit + DT
Lead time agreed; request number 1 always takes 17-19 seconds; and # 1 <1sec.
I am using Godaddy VPS Windows Server 2008 Economy
Example table:
id | idgps_unit | dt | location
1 | 1 | 2012-01-01 | 1
2 | 1 | 2012-01-02 | 2
3 | 2 | 2012-01-03 | 3
4 | 2 | 2012-01-04 | 4
5 | 3 | 2012-01-05 | 5
source
share