CODEWITHSUNDEEP
mysqlindexing
David542
David542

Reputation: 110163

Composite INDEX vs individual INDEXES

I have the following query:

SELECT count(*) from main where platform_id='ACME' AND master_id IS NOT NULL;

Looking at my database, I have indexes on both platform_id and on master_id. Running this query took 8.1s.

I then added a composite INDEX:

ALTER TABLE main ADD INDEX (platform_id, master_id);

When I ran the query again, it only took 61.6ms, over 100x faster! My question is why does it make such a big difference in having both columns indexed together as opposed to two individual indexes in the above query?

Upvotes: 1

Views: 535

Answers (1)

ratsbane
ratsbane

Reputation: 890

MySQL can only use one index in a case like that. It will choose either the platform_id index or the master_id index, depending on which one has the highest cardinality (number of distinct values in the index.) You can see the cardinality of each index with SHOW INDEXES. It is possible that the values MySQL has stored for cardinality aren't correct, in which case you can force it to recalculate them with ANALYZE TABLE main, but probably the results you're seeing are just because the distribution of unique values in those two indexes are such that it's not picking the most efficient one. Using a composite index as you've done here is probably the best strategy.

Upvotes: 1

Related Questions