MAX() Function with Group by is very slow

Question

My query is really slow; It takes 17 seconds to execute. It hits 100% CPU on Godaddy VPS. Any ideas what can be done?

This query:

         SELECT
             `gps_unit_location`.`idgps_unit`,
             MAX(`gps_unit_location`.`dt`) dtmax
         FROM `gps_unit_location`
         GROUP BY 1

Explain

id='1', select type='SIMPLE', table='gps_unit_location', type='index', possible keys=NULL, key='fk_gps2', key len='5', ref=NULL, rows='368668', extra=''

How many records are in the table? – Dan Bracuk Feb 16 '13 at 20:20 — Dan Bracuk, Feb 16 '13 at 20:20
As explain shows: `rows='368668'` – Andrew Feb 16 '13 at 20:21 — Andrew, Feb 16 '13 at 20:21
How many rows do you get in the output? – Bulat Feb 16 '13 at 20:44 — Bulat, Feb 16 '13 at 20:44

score 3 · Accepted Answer · edited May 23 '17 at 12:01

3

An index on (idgps_unit, dt) could make the query a lot faster.

You could just extend the index on idgps_unit, by changing:

KEY `fk_gps2` (`idgps_unit`),

to

KEY `fk_gps2` (`idgps_unit`, `dt`),

(According to this SO question, key is a synonym for index.)

edited May 23 '17 at 12:01

Community

1
1

answered Feb 16 '13 at 20:01

Andomar

232,371
49
380
404

After I add the index, should I run any command to re-index the existing data? – Andrew Feb 16 '13 at 20:03
The database will ensure that indexes are always up to date. Reindexing is only required if you want to defragment disk space. – Andomar Feb 16 '13 at 20:04
are you sure there's no need for rebuilding statistics? – Sebas Feb 16 '13 at 20:05
1

@Sebas: A newly created index will have perfect statistics. Have a look at [this MySQL doc](http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estimation.html) – Andomar Feb 16 '13 at 20:07
Hmm this document is unclear. Are you sure of that? I know this is different but under Oracle you ought to rebuild statistics after index creation. – Sebas Feb 16 '13 at 20:14
I added indexes (I changed the create table statement in question); Seems like query went from 17sec to 12sec... Still too slow... – Andrew Feb 16 '13 at 20:20
1

we're getting into fine tuning now. The next step is to debug the hardware :-( – Sebas Feb 16 '13 at 20:29
This seems to be an issue with Godaddy VPS. I recreated data on my local machine, it takes 1sec to execute. – Andrew Feb 16 '13 at 21:03

MAX() Function with Group by is very slow

1 Answers1