MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize Query with multiple joins
Posted by: Jørgen Løland
Date: March 24, 2011 06:53AM

Hi Angel,

The query plan for this query looks pretty good to me. MySQL chooses to do index ref lookups in all but the first table in the join sequence and that's as good as it gets.

When you query for distinct d.id, MySQL has to sort the join result on the d.id column to do distinct-filtering. A temporary table is needed to do this sorting.

It's hard to say from the query you wrote since there's no table aliased "d", but if it happens to be an alias for "dest" this join can never produce duplicates for d.id and you might as well remove "distinct". It does not make sence to me that an otherwise equal query but without distinct should be less efficient.

Be aware that since you do not provide an ORDER BY clause, it is non-deterministic which 40 rows will be returned by this query.

Hope this helps,
Jørgen Løland
Software Engineer,
MySQL group, Oracle
Trondheim, Norway

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize Query with multiple joins
3406
March 24, 2011 06:53AM


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.