MySQL Forums
Forum List  »  Optimizer & Parser

Re: Very slow query, help please
Posted by: Øystein Grøvlen
Date: January 30, 2013 05:12AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2651
January 29, 2013 10:18AM
Re: Very slow query, help please
1454
January 30, 2013 05:12AM
1405
January 30, 2013 08:38AM
1446
January 30, 2013 09:05AM


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.