Bug #39331 Optimizer makes slightly worse decisions, favouring non-prefixed indexes
Submitted: 9 Sep 2008 3:17
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.1.26-rc OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[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.