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

Please post table definition & EXPLAIN outputs.

The Scrum Meister
– The Scrum Meister

2011-10-28 07:21:52 +00:00
Commented Oct 28, 2011 at 7:21 — The Scrum Meister
– The Scrum Meister, Commented Oct 28, 2011 at 7:21

ajreal · Accepted Answer · 2011-10-28 07:58:34Z

2

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;

answered Oct 28, 2011 at 7:58

ajreal

47.4k11 gold badges98 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ricardo Over a year ago

you're right, i was expecting at least an error from sql, i guess it was spending time converting all the columns to integer to be able to compare to 1319677200.

Collectives™ on Stack Overflow

Multi column index slower than single column index in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related