Re: Optimize Query with multiple joins

[Jørgen Løland]

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