Announcement

Announcement Module
Collapse
No announcement yet.

muti-key index order

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • muti-key index order

    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.

    Comment


    • #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`)

      Comment


      • #4
        ,
        KEY `ipTO` (`ipTO`)
        ) TYPE=MyISAM;


        Number of records: 4,970,567

        Index is on ipFROM and ipTO fields.

        ipFROM and ipTO are integer numbers between 8 and 10 digits long.

        ----------------------------------------------------

        QUERY:

        SELECT countryLONG from ipcountry WHERE ipFROM <= 3521274926 AND ipTO >= 35212749

        Comment

        Working...
        X