Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

EF LINQ Query

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

select
    External1.FailureId,
    External1.col2,
    External1.col3,
from (select
          Inner1.FailureId,
          Inner1.col2,
          Inner1.col3,
      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.

select
    External1.FailureId,
    External1.col2,
    External1.col3,
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.

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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

share|improve this answer
    
+1, always get the plan to see what's actually going on. –  mrdenny Jan 21 '11 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? –  Nick Berardi Jan 21 '11 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 Jan 21 '11 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. :( –  Nick Berardi Jan 22 '11 at 2:58
add comment

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);
share|improve this answer
    
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. –  Nick Berardi Jan 21 '11 at 22:07
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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