mysql question

[Princeton]

Is there a better way to rewrite (optimize) this sql code? (NOTE: Some parts have been ommitted):
[sql]SELECT thread.threadid, thread.title, thread.postusername, thread.postuserid, thread.lastpost, forum.forumid
FROM thread AS thread
LEFT JOIN forum AS forum ON ( thread.forumid = forum.forumid )
WHERE thread.forumid
IN ( 25, 57, 223, 56, 88, 55, 58, 59, 60, 61, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74 , 75, 76, 77, 78, 79, 80, 81, 89, 116, 178, 217, 218, 219, 220, 221, 222, 226, 231, 229, 227, 228, 230) AND thread.visible =1
ORDER BY lastpost DESC
LIMIT 16 [/sql]
The reason I ask is because it can take some time if rows > 50,000

thank you

[Zachery]

Originally Posted by princeton

Is there a better way to rewrite (optimize) this sql code? (NOTE: Some parts have been ommitted):
[sql]SELECT thread.threadid, thread.title, thread.postusername, thread.postuserid, thread.lastpost, forum.forumid
FROM thread AS thread
LEFT JOIN forum AS forum ON ( thread.forumid = forum.forumid )
WHERE thread.forumid
IN ( 25, 57, 223, 56, 88, 55, 58, 59, 60, 61, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74 , 75, 76, 77, 78, 79, 80, 81, 89, 116, 178, 217, 218, 219, 220, 221, 222, 226, 231, 229, 227, 228, 230) AND thread.visible =1
ORDER BY lastpost DESC
LIMIT 16 [/sql]
The reason I ask is because it can take some time if rows > 50,000

thank you

Looks like get new or getdaily or just a search ?

[Xenon]

well, it's using an index and everything, so not much place to optimize.

but i see you are joining the forumtable, which is actually useless here, as you just want to get the forumid which is stored in the threadtable as well:

[sql]SELECT thread.threadid, thread.title, thread.postusername, thread.postuserid, thread.lastpost, thread.forumid
FROM thread AS thread
WHERE thread.forumid
IN ( 25, 57, 223, 56, 88, 55, 58, 59, 60, 61, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74 , 75, 76, 77, 78, 79, 80, 81, 89, 116, 178, 217, 218, 219, 220, 221, 222, 226, 231, 229, 227, 228, 230) AND thread.visible =1
ORDER BY lastpost DESC
LIMIT 16 [/sql]

if you really need some foruminfos and just removed them in your query, then i suggest to use two queries.

sometimes two queries can be much faster then a big one, especially if joins are in the game

[Princeton]

thanks Xenon - appreciate the input

I didn't think it would be possible to optimize but I thought I'd ask.

Oh yea - thanks for the new eyes regarding the "forumtable" ... I didn't even notice that. :up:

Zach,
If you are wondering what this is about ...
I'm fetching the last 16 threads from a specific section of my forum; and, this section has many sections.
Many sections + many threads = slow (it doubles my generation time)

Currently, my page is generated in .1 second with the above sql it will jump to .2 seconds.

I have thought of an alternative way to fetch the lastpost - going to try it out now.