MySQL Forums
Forum List  »  Performance

Re: Dead query
Posted by: Øystein Grøvlen
Date: January 03, 2013 07:48AM

Hi Pablo,

It's a bit hard to say without seeing the query plan (please, post the output of EXPLAIN for this query), but I suspect that your problems can be related to the
sub-query in the FROM clause (so-called derived table).

The result of this sub-query will be materialized in a temporary table, and later this temporary table will be joined with the rest of the tables in the query. Since the join operation for this derived table is LEFT JOIN, MySQL has to process the left operand of the join first. This means that to do the JOIN, one will have to do look-ups into the derived table to find matching rows. However, prior to MySQL 5.6, the temporary table is not indexed, so each look-up will result in a table scan.

To overcome this issue, you could try to eliminating the sub-query by merging it into the main join. An alternative, if possible without changing the meaning of the query, is to replace LEFT JOIN with INNER JOIN. Not using LEFT JOIN where it is not strictly necessary is a generally good advice, since LEFT JOIN restricts the order in which tables may be joined.

You could also try downloading the MySQL 5.6 Release Candidate to see whether MySQL 5.6 will solve your problems.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote


Subject
Views
Written By
Posted
3252
December 21, 2012 08:00AM
1095
December 22, 2012 03:27PM
Re: Dead query
1258
January 03, 2013 07:48AM


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.