I'm using Entity Framework with MySQL, and my Linq Query:
db.Persons.Where(x => x.Surname.StartsWith("Zyw")).ToList();
..is producing the SQL:
SELECT PersonId, Forename, Surname FROM Person WHERE (LOCATE('Zyw', Surname)) = 1
...and it would seem that this doesn't make use of the index on Surname.
If LOCATE is replaced with the equivalent LIKE, the query speedily returns the required results. As it is it takes all afternoon.
Why is Entity Framework and its connecting drivers opting for this wierd LOCATE function / how can I make it use LIKE instead / why is MySQL making a poor index decision for the LOCATE function / how can I make it better?
I'm afraid I was guilty of over simplifying my code for this post, the Linq producing the error is in fact:
var target = "Zyw"; db.Persons.Where(x => x.Surname.StartsWith(target)).ToList();
If target term is hard coded, the SQL generated does indeed use LIKE, but with a variable term the SQL changes to use LOCATE.
This is all using the latest generally available MySQL for Windows as delivered by MySQL Installer 5.6.15.
A couple more notes to go with the bounty; am using:
The Entity Framework code is generated database first style.
I've also tried it with the latest connector from Nuget (MySql.Data 6.8.3) and the problem is still there.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `Person` ADD INDEX `person_idx_locate_surname` (`locate_zyw_surname`);
SELECT Person.PersonId, Person.Forename, Person.Surname FROM Person WHERE Person.locate_zyw_surname = 1