MySQL Performance: MySQL => muti-key index order

muti-key index order [message #552]

Tue, 09 January 2007 01:34

rhuddleston
Messages: 11
Registered: August 2006

Junior Member

I've always thought that when creating muti-key indexes the column with the highest cardinality should be placed first.

For example For this query:

select count(*) from transactions where tbl=1 and id=5000;

id=5000 will match 100x less rows than tbl=1. Does it matter if the index is (tbl,id) or (id,tbl) when matching on both. From observing this in a production environment it seems to me the better index is (id,tbl) as I see fewer of those showing up in the slow-log than when the index was reversed. Though when I try to prove this through testing It's difficult to see much difference. If (id, tbl) is a better choice why is this? Can we prove this? What is mysql doing internally different when accessing the two indexes?

-Ryan

Report message to a moderator