MySQL not using multiple column index for some queries

Question

I have a table with several million records on MySQL in an MyISAM table. Very simplified, it's like this:

CREATE TABLE `test` (
  `text` varchar(5) DEFAULT NULL,
  `number` int(5) DEFAULT NULL,
  KEY `number` (`number`) USING BTREE,
  KEY `text_number` (`text`,`number`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

It's populated with this data:

INSERT INTO `test` VALUES ('abcd', '1');
INSERT INTO `test` VALUES ('abcd', '2');
INSERT INTO `test` VALUES ('abcd', '3');
INSERT INTO `test` VALUES ('abcd', '4');
INSERT INTO `test` VALUES ('bbbb', '1');
INSERT INTO `test` VALUES ('bbbb', '2');
INSERT INTO `test` VALUES ('bbbb', '3');

When I run the following query:

EXPLAIN SELECT * FROM `test` WHERE (`text` = 'bbbb' AND `number` = 2)

It returns 'number' as the key to use. But the following query:

EXPLAIN SELECT * FROM `test` WHERE (`text` = 'bbbb' AND `number` = 1)

Returns 'text_number' as key to use, which would make more sense to me as this combined key matches exactly with the 2 columns in the WHERE. On these amount of records the performance isn't an issue, but on several million records the query which uses the 'text' index takes 4 seconds, and the one that uses 'text_number' index is finished in several milliseconds.

Is there a logical explaination for this? How can I change the index that MySQL uses the index? I know I can use USE INDEX but I want MySQL to be able to find the best plan to execute the query. This is on MySQL 5.1 and 5.5, same results.

SQL Fiddle shows the same index being used for both (sqlfiddle.com/#!2/d8b239/2). — Gordon Linoff
– Gordon Linoff, Commented Sep 12, 2013 at 14:30
I'm voting to move this to dba.stackexchange, this is bizarre and I think it needs someone who knows a vast amount about MyISAM index selection. In this fiddle the data is perfectly symmetrical (i.e. the same 4 numbers for each text), when searching for 6 of the combinations it uses the text_number index, when searching for 2 it uses number, I have no idea why. I thought it might be due to thresholds at which statistics get updated, but I moved the creation of the indexes to after the data was inserted to avoid this. — GarethD
– GarethD, Commented Sep 12, 2013 at 16:33
In addition, the insert order does not make any difference (sqlfiddle.com/#!2/1ba8d/1), the order in the index does not make any difference (sqlfiddle.com/#!2/4ec901/1), and it appears to always happen on the second lowest number, rather than specifically on the number 2, sqlfiddle.com/#!2/b8753/2, sqlfiddle.com/#!2/c3f572/2. However, you would expect it to use the number index on the second highest number when the index is reversed, but it doesn't it is still on the second lowest. sqlfiddle.com/#!2/30020/1. A complete mystery to me. — GarethD
– GarethD, Commented Sep 12, 2013 at 16:47

Antoan Milkov · Accepted Answer · 2013-09-12 15:07:36Z

1

If you expect some of the queries to be game changer for the performance it is good idea to use you own indexes, like:

EXPLAIN SELECT * FROM `test` USE INDEX (number) WHERE (`text` = 'bbbb' AND `number` = 1);
or
EXPLAIN SELECT * FROM `test` USE INDEX (text_number) WHERE (`text` = 'bbbb' AND `number` = 1);

Generally, you can relay on the built-in query optimizer for most of the queries, but for the crucial or problematic ones it is good idea to look closer.

answered Sep 12, 2013 at 15:07

Antoan Milkov

2,23821 silver badges31 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:11:08Z

1

Two things that could improve your performance:

ANALYZE TABLE to update your optimizer's index statistics so it knows the current key distribution. You can control the optimizer's statistics generation in a number of ways.
Create a second covering index with the fields reversed. In the real world, this involves figuring out the optimal one or two covering indexes for a set of three or four columns, and that's something you arrive at through benchmarking and EXPLAIN analysis. Have a look at this answer for some advice on doing that.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Sep 12, 2013 at 15:09

Jeremy Smyth

23.6k2 gold badges57 silver badges66 bronze badges

1 Comment

Thijs Over a year ago

I tried to ANALYZE the table, but it wouldn't matter. Can give the second option a try though!

Collectives™ on Stack Overflow

MySQL not using multiple column index for some queries

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related