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?
I’ve been breaking my head with this for a while now. I have a query, which is very slow and I’m trying to find a way to speed it up. It takes several seconds (usually between 3 and 10) to run the query.
CREATE TABLE ipcountry ( ipFROM double unsigned NOT NULL default ‘0’, ipTO double unsigned NOT NULL default ‘0’, countrySHORT char(2) NOT NULL default ‘’, countryLONG varchar(255) NOT NULL default ‘’, REGION varchar(128) default NULL, CITY varchar(128) default NULL, ISP_NAME text,
PRIMARY KEY (ipFROM,ipTO),
KEY ipFROM (ipFROM)