Help requested optimising query with subquery

[Christian Seaman]

Hi,

I have the following query, but it's running slow despite everything being properly indexed. I could probably work around this by running the subqueries in PHP first then inserting the returned values into this query, but I was wondering if there is some way to rewrite the SQL to be more efficient (perhaps using JOINs?).

Here goes:

SELECT DISTINCT p.summary_text summary_text
FROM companies c2, matches m2, pages p
WHERE m2.company_id = c2.company_id
AND c2.company_id = p.company_id
AND p.active = 1
AND m2.category_id IN (
SELECT DISTINCT m.category_id
FROM ticks t, users u
LEFT JOIN matches m on t.company_id = m.company_id
WHERE u.value = '12345'
AND u.u_id = t.u_id
)
AND m2.company_id NOT IN (
SELECT DISTINCT t.company_id
FROM ticks t, users u
WHERE u.value = '12345'
AND u.u_id = t.u_id
AND t.company_id IS NOT NULL
)
LIMIT 10

Basically, I'm trying to find pages that match the categories ticked, but excluding the companies that have been ticked.

Thanks in advance!


C


(Edited to correct typo)



Edited 2 time(s). Last edit at 08/08/2007 06:24AM by Christian Seaman.