Subqueries too slow

[Andreas Schlicker]

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