MySQL Forums
Forum List  »  Optimizer & Parser

Subqueries too slow
Posted by: Andreas Schlicker
Date: February 06, 2006 11:08AM

Hi all,

I have the following query:
SELECT a.id, a.acc, a.name FROM a
WHERE (a.id = ANY
(SELECT b.a_id FROM b
INNER JOIN c ON b.c_id=c.id
WHERE c.acc="X")
AND (a.id = ANY
(SELECT b.a_id FROM b
INNER JOIN c ON b.c_id=c.id
WHERE c.acc="Y"))

This query is really slow if the table a is large.
I tryed to rewrite it without the subquery:
SELECT a.id, a.acc, a.name FROM a, b, c WHERE
(a.id = b.a_id AND b.c_id=c.id AND c.acc="X") AND
(a.id = b.a_id AND b.c_id=c.id AND c.acc="Y")

However, this query returns nothing.
Do you have any suggestions on how to improve the first query or correct the second one?

I'm running MySQL 4.1.9-max on a SunOS 5.9 with 4 CPUs.

Thank you very much
Andreas

Options: ReplyQuote


Subject
Views
Written By
Posted
Subqueries too slow
5467
February 06, 2006 11:08AM
2764
February 06, 2006 11:54AM
2539
February 06, 2006 02:17PM
2555
February 07, 2006 07:06AM
2564
February 07, 2006 08:38AM
2651
February 10, 2006 03:18AM
2224
February 10, 2006 08:51AM
2272
February 10, 2006 09:49AM
2486
February 10, 2006 10:43AM


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.