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
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
Comment