Why does this query take so long??

[Tigga]

Hopefully someone here can help me with this, because I'm about out of ideas.

I use the following query to pull the last 10 threads on pretty much every page on my site...

PHP Code:

SELECT
thread.threadid,thread.title,replycount,postusername,postuserid,thread.dateline,views,lastposter,lastpost
    ".iif($showthreadicon,',thread.iconid as iconid,icon.title as icontitle,icon.iconpath as iconpath','')." 
    FROM thread 
    ".iif ($showthreadicon,'LEFT JOIN icon ON icon.iconid=thread.iconid','')." 
    WHERE open<>10 $iforumperms 
    ORDER BY thread.lastpost DESC 
    LIMIT 10 


The problem with the speed seems to arise from the $iforumperms part, which ends up looking something like this:

PHP Code:

AND forumid=1 OR forumid=5 OR forumid=9 OR forumid=2 OR forumid=3 OR forumid=4... etc 


I added an index on the thread.lastpost column which helped with the speed, but only if I remove the $iforumperms part from the query. If I don't have the $iforumperms it takes about 0.001 seconds to execute that query, but when the $iforumperms part is added back in it takes closer to 0.4 seconds to load. I tried adding an index on the thread.forumid just incase, but that didn't help at all. Does anyone have any ideas of another way I could speed that up and keep the forum permissions as well? Any help would be much appreciated.

[LangTuDaTinh]

how many forums are included? the more forums ...the longer it takes to look for latest threads.

[amykhar]

Tigga, it honestly depends on how many forums you don't want to show. For example, on my board, I only have a few private forums that nobody should see. So, it might be more efficient for me to hard code those in instead of basing them on forumpermissions. Then, my query would say:

WHERE open<>10 $iforumperms and forumid not in (1,3,4)

That might help speed things up a bit. However, if you have a ton of forums that are only visible to select users, you might be stuck with the slower query.

Typically, I would simply exclude forums that guests shouldn't see and be happy with that and not worry that the latest private threads don't show up in the list.

Amy

[amykhar]

Also, I would personally kill the thread icon part and get rid of that join. But that's just a personal preference.

Amy

[Tigga]

Thanks Lang and Amy. I was hoping there would be another way to pull the threads without sacrificing the forum permissions, and I think I found one. I edited the permission system just a little and changed it so it will exclude private forums instead of including all forums that aren't private, and it seems to work much better. The query's down to about 0.001 seconds and it seems to be working perfectly.