MySQL Forums
Forum List  »  Performance

Re: Slow subquery performance
Posted by: Peter Zaitsev
Date: November 05, 2004 12:36AM


If you're using "=" MySQL is sure it will get only one row from subselect , furthermore this subselect depens only on constants so MySQL is able to use special optimization to "pre-execute" sub select and effectively replace it with constant.

MySQL however can't do the same with IN. At this point it is only able to scan first table
and execute this subselect for each row.

One of optimizations possible (not implemented yet) would be to pre-read this sub-select into temporary table and handle the query the same way as with IN(<constants list>) which would allow to do range analyses and check if it is efficient to read back the rows for each
IN values or do full table scan and perform matches against the list.

Rewriting to the join is probably good optimization, the other idea would be to execute the
subquery and create query with matching constant IN list on the application level

Options: ReplyQuote

Written By
November 04, 2004 05:00PM
Re: Slow subquery performance
November 05, 2004 12:36AM

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.