Let me give you a little setup. We have a InnoDB table on MySQL 5.1 with close to 20 million records, no foreign keys, and proper indexes for queries we do. We are using the latest 6.3.5 MySQL release for the .NET Entity Framework. Normally we are use to dealing with SQL Server and the Entity Framework, but on this project we decided to give MySQL a try.

I have some theories on the issue but let me first do the code setup


var failsForAcct1001 = db.Failures.Where(x => x.AccountId == 1001);
/* farther down and later on in the code */
return failsForAcct1001.OrderBy(x => x.FailureId).Take(50);

MySQL Code Generated

Please ignore the column names they aren't nessisary to understand the problem

from (select
      from Failures Inner1
      where External1.AccountId = 1001
    ) External1
order by External1.FailureId
limit 50

This SQL being generated is very similar to what happens in SQL Server, and SQL handles it with out a problem. And this isn't just an Entity Framework issue, when I take this query and execute in MySQL Workbench it also times out. However the following query returns in under a half second.

from Failures External1
where External1.AccountId = 1001
order by External1.FailureId
limit 50

I assume this performance problem has to do with the fact that MySQL is trying to execute the inner query which pulls back all the records in the database and then tries to sort and take 50, instead of doing this all as one operation like the second query does.

I really just want to confirm my observations, and ask if there is anything I can do to get the first SQL statement to execute faster, without modifying the query in any way.

  • 1
    That is plain disappointing of the connector, needlessly using a subquery to combine WHERE and ORDER BY! I presume you have an index like AccountId, FailureId. That would make the subquery-free version efficient, whereas the subquery version would be slow, needing to fetch all records for account 1001. I have certainly never been impressed with the MySQL connector's SQL generation so far...
    – Timo
    Commented Apr 29, 2017 at 9:59
  • Indeed, that ORM generated SQL is a disaster. It is an unnecessary subquery, and as a bonus, it is the worst kind: correlated. Commented Jan 27, 2023 at 4:56

4 Answers 4


Step 1 - get an explain plan, in particular EXPLAIN EXTENDED will show you what SQL the query optimizer actually generated. Maybe additional indexes will help - but if you "outsource" your SQL generation then you really are at the mercy of whatever was easier for the ORM developer, not what was best for your application. Another thing you might try is in-memory temp db, as this can help with sorts, and derived tables, which you do have...

  • +1, always get the plan to see what's actually going on.
    – mrdenny
    Commented Jan 21, 2011 at 22:07
  • 1
    I would get the plan, explain won't execute because the query always times out. Is there anyway to get a plan with out having it execute the query? Commented Jan 21, 2011 at 22:22
  • 1
    That's odd, the docs say that EXPLAIN generates the plan but doesn't actually execute it. I wonder if you've hit a bug that has stalled it in the optimizer? Give it a try on a different/the latest MySQL and see if you get anything different?
    – Gaius
    Commented Jan 21, 2011 at 22:35
  • Well I don't have another database with this much data in it. So I can't really test under same circumstances. :( Commented Jan 22, 2011 at 2:58

MariaDB 5.3 can handle such queries.

The optimization is covered by the switch optimizer_switch='derived_merge=on' in the current 5.3.2 release, and this optimization will become default (no setting necessary) in the imminent 5.3.3 release

More info on optimizer in 5.3


I know LINQ queries are composable, but have you tried playing with the order of LINQ operations to see if it could affect the query generator? This question may be better served over on Stack Overflow.

var failsForAcct1001 = db.Failures.Where(x => x.AccountId == 1001).OrderBy(x => x.FailureId).Take(50);
  • As I said /* farther down and later on in the code */ they are broken apart, so yes combining it like that would fix the problem, but because of how the request is structured I don't have this luxury. Because the OData provider adds the OrderBy and Take parts on. I was trying not to get too technical about the program process, because it really didn't matter. But that comment I put between the two statements is actually happening through an OData REST request. So everything after the where statement I have no control over. Commented Jan 21, 2011 at 22:07

Your issue is due to the sub-query.

There are two problems with this:

  • MySQL does not know how to project the outer queries predicates, limits etc. to the inner query, so the inner query ends up being fully materialized ( and then, as a second step, filtered based upon the outer queries. (http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html)
  • The issue is further compounded by the fact that the MySQL EF Connector does not do as good of a job at optimizing / simplifying the query as microsoft's does. Microsoft's EF connector will collapse the inner and outer query into 1 query (whenever it can). MySQL's does not do this with the same accuracy as Microsoft's.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.