muti-key index order

MySQL & MariaDB Other MySQL® Questions
1

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

2

Ryan,

I would normally put more selective column first.

The performance difference should not be major though in most cases.

The difference is internally MySQL needs to compare values and if they do not match and difference is in the first bytes it is faster.

Think for example comparing non matching strings - if first character is different or only character 100 is quite a difference.

3

Hi all,

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.

TABLE STRUCTURE:

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)