Re: Very slow query, help please

[Øystein Grøvlen]

Hi Jaume,

EXPLAIN plan does not show up on my screen, but I would guess that you problem is related to the combination of LEFT JOIN and derived tables (sub-queries in from clause). In MySQL 5.5 and earlier, the derived tables like T3 will not have any indexes. Hence, all rows of T3 will have to be scanned for each row of proveidors_treballadors. Since LEFT JOIN is used, the join order is fixed. With ordinary JOIN, the order could be swapped, and an index on proveidors_treballadors.id_prov_treb could be used to perform the join. Do you really need the LEFT JOIN? That is, do really need to get the rows from proveidors_treballadors that does not have any matches in albarans?

You have similar issues with T1 and T2.

Note that this issue is fixed in MySQL 5.6 where indexes will be created for derived tables when necessary.

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