[Solved] Entity Framework converts StartsWith to MySQL\'s Locate, MySQL\'s Locate doesn\'t use index
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Entity Framework converts StartsWith to MySQL\'s Locate, MySQL\'s Locate doesn\'t use index

Database type:

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?

Update:

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.

Update:

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.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Algebric Transformation On Indexable Expressions (modified query below): When algebric modifications are applied to an indexable expression, the database won't be able to utilize the index efficiently for search operations.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Index Function Calls Using Generated Columns (modified query below): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index to optimize the search. Creating and indexing a generated column (supported in MySQL 5.7) will allow MySQL to optimize the search.
Optimal indexes for this query:
ALTER TABLE `Person` ADD INDEX `person_idx_locate_surname` (`locate_zyw_surname`);
The optimized query:
SELECT
        Person.PersonId,
        Person.Forename,
        Person.Surname 
    FROM
        Person 
    WHERE
        Person.locate_zyw_surname = 1

Related Articles



* original question posted on StackOverflow here.