MySQL Forums
Forum List  »  Optimizer & Parser

mysql subquery query optimization question
Posted by: Yong Yim
Date: January 12, 2013 09:24AM

We have a MySQL table that has approx. 1 million records. We recently upgraded our custom application to use Microsoft Entity Framework (EF), which abstracts out the datastore (in our case MySQL). When our application looks up a single record from the table, I noticed the EF issues a query like the following.

select some_alias.* from (Select * from MyTable) as some_alias where some_alias.key = 'some_key'

instead of

select * from MyTable where key = 'some_key'

The problem is that the first query takes more than 40 seconds to execute while the second query takes 15 milliseconds.

At first I thought it was some kind of bug in EF for constructing the query this way. After further research, however, it turned out that the first query is as fast as the second query when using other database engines (so far tested on Oracle and MS SQL Server) so I can't blame EF entirely. :(

My first question is why is the first query much slower than the second one in MySQL? Is it some kind of an optimizer issue?

My second question is is there something I can do from the MySQL side to make the first query as fast as the second one? The reason I ask is because I've exhausted every possibility I could think of to address the issue from the application code side but have found no solution/workaround.

Any suggestion/assistance would be greatly appreciated!

Options: ReplyQuote

Written By
mysql subquery query optimization question
January 12, 2013 09:24AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.