Bug #39331 Optimizer makes slightly worse decisions, favouring non-prefixed indexes

• Submitted: 9 Sep 2008 3:17
• Reporter: Morgan Tocker
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.1.26-rc
• OS: Any
• CPU Architecture: Any

[9 Sep 2008 3:17] Morgan Tocker

Description:
On a query (SELECT * FROM City WHERE Name = 'Paris'), lets say I have two indexes:

a) one on Name
b) one on Name(8)

The optimizer in both MyISAM and InnoDB seems to pick (a), although since string_length(paris) < 8, you would think that Name(8) should suffice.

A few mysqlslap tests seem to indicate that Name(8) is indeed faster.

How to repeat:
[student@localhost ~]$ mysqlslap --create-schema=world -q "SELECT * FROM City FORCE INDEX(eight) WHERE Name = 'Paris'" -c 100 -i 1000
Benchmark
	Average number of seconds to run all queries: 0.049 seconds
	Minimum number of seconds to run all queries: 0.004 seconds
	Maximum number of seconds to run all queries: 0.088 seconds
	Number of clients running queries: 100
	Average number of queries per client: 1

[student@localhost ~]$ mysqlslap --create-schema=world -q "SELECT * FROM City WHERE Name = 'Paris'" -c 100 -i 1000
Benchmark
	Average number of seconds to run all queries: 0.056 seconds
	Minimum number of seconds to run all queries: 0.012 seconds
	Maximum number of seconds to run all queries: 0.155 seconds
	Number of clients running queries: 100
	Average number of queries per client: 1

Suggested fix:
Prefer prefixed indexes more.