rossco_2005
09-28-2008, 11:35 PM
I need help with optimizing queries with indexes.
I try my best but whatever combinations I try the query is slow.
Here is an example query:
SELECT COUNT(nzb.threadid) as threads
FROM thread as thread
LEFT JOIN nzb as nzb
ON nzb.threadid=thread.threadid
WHERE thread.forumid IN (80,120,106,108,107)
AND sticky = 0
AND visible IN (0,1,2)
OR
SELECT COUNT(nzb.threadid) as threads
FROM thread as thread
LEFT JOIN nzb as nzb
ON nzb.threadid=thread.threadid
WHERE thread.forumid IN (80,120,106,108,107)
AND sticky = 0
AND visible IN (0,1,2)
AND thread.iconid IN(30,34,36,67,73,99,100,102,104,75,76,78,97,98,10 7,106,33)
They don't need to have the sticky or visible columns in the WHERE, that was just something I thought might help me to hit the forumid index in thread table.
Anyway what's the recommended indexes keeping in mind this query ^.
Thanks a lot for any help you can give me. :)
I try my best but whatever combinations I try the query is slow.
Here is an example query:
SELECT COUNT(nzb.threadid) as threads
FROM thread as thread
LEFT JOIN nzb as nzb
ON nzb.threadid=thread.threadid
WHERE thread.forumid IN (80,120,106,108,107)
AND sticky = 0
AND visible IN (0,1,2)
OR
SELECT COUNT(nzb.threadid) as threads
FROM thread as thread
LEFT JOIN nzb as nzb
ON nzb.threadid=thread.threadid
WHERE thread.forumid IN (80,120,106,108,107)
AND sticky = 0
AND visible IN (0,1,2)
AND thread.iconid IN(30,34,36,67,73,99,100,102,104,75,76,78,97,98,10 7,106,33)
They don't need to have the sticky or visible columns in the WHERE, that was just something I thought might help me to hit the forumid index in thread table.
Anyway what's the recommended indexes keeping in mind this query ^.
Thanks a lot for any help you can give me. :)