Multi column index slower than single column index in mysql

Question

I have a myisam table with a primary key spanning 5 columns. I do a select using a WHERE on every of those 5 columns ANDed. Using the primary key (multicolumn index) it takes 25s, using a single index in one of the columns it takes 1 sec. I did a profiling and most of the 25s is taken in “Sending data” stage. The primary key has cardinality of about 7M and the single column about 80. Am i missing somehting?

CREATE TABLE `mytable` (
  `a` int(11) unsigned NOT NULL,
  `b` varchar(2) NOT NULL,
  `c` int(11) unsigned NOT NULL,
  `d` varchar(560) NOT NULL,
  `e` varchar(45) NOT NULL,
  PRIMARY KEY (`a`,`e`,`d`,`b`,`c`),
  KEY `d` (`d`),
  KEY `e` (`e`),
  KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


EXPLAIN SELECT * FROM mytable USE INDEX (PRIMARY)
WHERE a=12 AND e=1319677200 AND d='69.171.242.53' AND b='*' AND c=0;

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  i   ref PRIMARY PRIMARY 4   const   5912231 Using where


EXPLAIN SELECT * FROM mytable
WHERE a=12 AND e=1319677200 AND d='69.171.242.53' AND b='*' AND c=0;

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  i   ref PRIMARY,d,e,b   d   562 const   158951  Using where

ajreal ajreal · Accepted Answer · 2011-10-28T07:58:34

The problem is caused by casting,
try quote every varchar column b,d,e

SELECT * FROM mytable USE INDEX (PRIMARY)
WHERE a=12 AND e='1319677200' AND d='69.171.242.53' AND b='*' AND c=0;

Multi column index slower than single column index in mysql

1 Answers