Mysql speed up max() group by

问题:

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.

回答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)

回答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.

回答3:

See what mysql manual says about speeding up the max() , min() query

MySQL uses indexes for these operations:

To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant.