CODEWITHSUNDEEP
mysqlsqlquery-optimization
Myforwik
Myforwik

Reputation: 3588

mysql: improve group by query performance?

Is there anyway to increase the performance of a query like this:

  SELECT MAX(my_index)  
    FROM my_table 
GROUP BY my_type

I have an index on my_index and another on my_type. The query takes about 10 seconds once I have millions of records. my_index is unique to each record and autoincrements.

I figure there should be away to do this query quickly if I create a composite index, but I am not sure how. The same query with a composite index on my_index , my_type took twice as long than without it...

Upvotes: 1

Views: 4415

Answers (2)

RET
RET

Reputation: 9188

I think you want an index on my_type, my_index rather than the other way around. I'm not sure OTTOMH if MySQL supports bi-directional traversal of indexes, but if it doesn't, you want to create the index on my_type, my_index DESC so that the record you're looking for is the first one in the index, not the last.

Upvotes: 0

Itay Moav -Malimovka
Itay Moav -Malimovka

Reputation: 53606

composite index should be on (my_type,my_index) in that order. If you can make that index the PK, even better (as it orders it physically on the disk).
You have not specified how many records u have, if you have 1000000000000 records, then I would say 10 seconds is pretty fast.

Upvotes: 3

Related Questions