Bug #40295 Prefix of multi-column indexes get used improperly

• Submitted: 24 Oct 2008 3:01
• Reporter: Harrison Fisk
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.0.60sp1
• OS: Any
• CPU Architecture: Any
• Tags: Optimizer

[24 Oct 2008 3:01] Harrison Fisk

Description:
MySQL has a poor cost model when using only a prefix of a multi-part index.  This can lead it to use to the prefix of the index when it really shouldn't which can lead to poor execution times.  Instead, it should opt to do a full table scan.

This appears to occur for both MyISAM and InnoDB.

How to repeat:
CREATE TABLE `test_multi` (
  `a` int(11) default NULL,
  `b` decimal(10,8) default NULL,
  `c` text,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM;

# insert lots of data
INSERT INTO test_multi values (1, rand(), repeat('a', 10000));
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;
INSERT INTO test_multi SELECT 1, rand(), repeat('a', 10000) FROM test_multi;

# insert a few different values of a
INSERT INTO test_multi SELECT 2, rand(), repeat('a', 1000) FROM test_multi LIMIT 5000;

#analyze the table to get updated statistics
ANALYZE TABLE test_multi;

# see it is using the prefix of the index, even though it is selecting most of the table
explain SELECT max(c) FROM test_multi WHERE a = 1;

# see execution time
# this will take a very long time on most test systems
SELECT max(c) FROM test_multi WHERE a = 1;

# ignore the index
explain SELECT max(c) FROM test_multi IGNORE INDEX (a) WHERE a = 1;

# see execution time
# takes ~1 minute on my system
SELECT max(c) FROM test_multi IGNORE INDEX (a) WHERE a = 1;

Suggested fix:
Make MySQL do a full table scan (or pick a different index).  It looks like the optimizer doesn't work well with prefixes of indexes.