Mysql speed up max() group by

Question

I have an easy query for grouping rows which takes 0.0045 sec. for 300.000 rows

SELECT cid FROM table GROUP BY cid

When I add MAX() to query it takes 0.65 sec to return.

SELECT MAX(id) id, cid FROM table GROUP BY cid

How can I speed up this query? The query runs on my local host for testing. id = primary key and I have index on cid.

Answer 1

The reason is the difference between the two queries:

• Your first query will never touch the table - it wil rely on the index only
• Your second query actually needs to hit all rows

So to get back to the more optimal first case, you need an index, that can provide both: grouping by cid and min/maxing id. You could try to achieve this by creating an index on (cid,id)

Answer 2

I'd try adding a composite index on cid and id. This could possibly replace the existing index on just cid. I suggest you profile some typical queries to assess the impact of increasing the size of the existing index. The composite index contains exactly the data required to satisfy the query, so should minimise the work required.

MySQL uses cost-based optimization. The costing is based on the amount of i/o, hence if you can put in place an index on just the columns of interest this, should minimise i/o and lead to an optimal query.