MySQL Forums
Forum List  »  Performance

Re: how to make a simple subquery in mysql and high performance
Posted by: Rick James
Date: October 26, 2013 09:42AM

Please use SHOW CREATE TABLE, it is more descriptive than DESCRIBE.

Please use [ code ] and [ / code ] (without spaces) around code and output.

The construct
LEFT JOIN ( SELECT ... )
is inherently slow because it works this way...
1. The subquery SELECT is evaluated and stored in a temp table.
2. No index exists on this temp table (unless you are running 5.6)
3. LEFT implies that the left side comes first. So it has to reach into this temp table (Nested Loop Join) repeatedly
4. Without an index, that means doing a table scan of the temp table -- repeatedly. Slow.

You have at least two cases of LEFT JOIN ( SELECT ... )

Is LEFT necessary?

You could CREATE TEMPORARY TABLE ... SELECT ...; and add an INDEX to it. This is likely to be a big speedup.

Do you have any INDEXes on any of the tables?? (It is hard to tell.)

Do EXPLAIN on the SELECT.

(It may take another pass or two at this to get it working better.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: how to make a simple subquery in mysql and high performance
1059
October 26, 2013 09:42AM


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.