MySQL Bugs: #30883: Dependent Sub Queries Very Slow

Bug #30883	Dependent Sub Queries Very Slow
Submitted:	7 Sep 2007 5:28	Modified:	16 Nov 2008 17:26
Reporter:	Eugene Lee	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.27	OS:	Linux (CentOS 4.3)
Assigned to:		CPU Architecture:	Any
Tags:	dependent, slow, SUB QUERY

Description:
I have been trying to run a relatively simple subquery which is taking an inordinately long time to run.

There are 4 tables with varying row lengths between 50000 to 250000.  All tables have <= 8 columns.  All appropriate columns are indexed.

However, according to my slow log, the query is taking > 21000 seconds to execute.  

# User@Host: XXXXX[XXXXXX] @ XXXX.XXXXXX.XXXX [XXXXXXXXXXX]
# Query_time: 21374  Lock_time: 0  Rows_sent: 878  Rows_examined: 604523
SELECT 
t1.id
FROM TABLE1 t1, TABLE2 t2 
WHERE 
t1.t1id = t2.t2id 
AND NOT t1.id in 
(SELECT t3.t3id FROM TABLE3 t3, TABLE4 t4 WHERE t3.t3id = t4.t4id );

How to repeat:
Running the query above repeatedly shows this problem.  Changing things to include LEFT and/or RIGHT JOINS does not resolve the issue.

Thank you for a problem report. This is a know problem that should be fixed in MySQL 5.2.x (it is still at alpha stage now).

Thank you for a problem report. This is a know problem that should be fixed in MySQL 5.2.x (it is still at alpha stage now).

Is this fix included in the current 6.0 alpha release?  

Are there any other suggested workarounds?  In some instances, the query can be broken up and multiple tables can be created and then used to get around this, however there are several situations where this may not be possible.

Please, check if version 6.0.6 solves the problem for you.

Thanks for the update.  I will try to put this up on a test platform later this month and let you know.

Eugene

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".