I'm having difficulties getting a subselect to perform well. I've used
EXPLAIN to try to understand the problem, but can't see anything
wrong. I've also created appropriate indexes, but am wondering if there is
something else involved in my particular situation.
Here is my query:
SELECT COUNT(*) FROM quiksearch q
WHERE q.resource_status_id=1
AND q.org_id IN (
SELECT org_id FROM org_resource_type WHERE resource=12
);
Both tables are simple and small (5000 rows in quiksearch, 12000 in
org_resource_type).
q.org_id is an integer
I've tried this with three values for the constant in the inner subquery
n rows in subquery execution time
-- --------------- ---------------
12 301 3 sec
3 1136 182 sec
16 1129 7 sec
The subqueries themselves all execute in less than one second.
I also tried running the second subquery, saving the values in a list
(1,2,3...), and hard coding that in instead of a subquery, execution time
dropped to three seconds. i.e.
SELECT COUNT(*)
FROM quiksearch q
WHERE q.resource_status_id=1
AND q.org_id IN (
9,25,512,36,3,167,166,169,170,.........
);
I don't understand what is going on here, since the inner subquery runs
very fast, and the entire query also runs fast if I substitute the list of
returned values instead of a subquery.
Is there a way to make this query run faster? Are there tricks to
optimizing subqueries?