Any idea to optimize THIS query?

[cedivad]

I'm working hard on the load time of the pages of my script (script is my own, the only thing shared with vbulletin is the database). Expecially, i have big problems with this query:

select threadid from thread where forumid=1082 order by dateline desc limit 0, 25;

The query takes about 0.5 sec. The forum (forumid=1082) has about 120'000 topics. The full database has about 5 milion threads.

Executing the query without the "order" takes 0.00 seconds, but i need to order it!

Do someone have some ideas on how to fix this?

[luki]

Try adding index to this field.

ALTER TABLE `thread` ADD INDEX (`dateline`);

[mokujin]

try this:
SELECT threadid, forumid, dateline
FROM thread
WHERE forumid = 1082
ORDER BY dateline DESC
LIMIT 0 , 25

[Adrian Schneider]

Eek.

If you're only pulling 25 threads from 120,000, then you should restrict by dateline as well.

WHERE dateline >= " . TIMENOW - (86400 * 7) . "This would only pull threads created in the past week, which should be fine if the forum has any activity. This way you utilize the index and not kill the server.

[cedivad]

Thanks for the replies.
vBulletin by default have the index for "dateline" already setted. I noticed that the query takes 0.5 second only the second time i try it, the first the result is
25 rows in set (5.60 sec)
Witch is really bad.

I can't cut the topics older than a week, since that i should order it by dateline ASC, that was only an example.

This is the example where the query is executed: http://it.narkive.com/c/1082 (loading 250 topics instead of 25 don't take any longer!). As stated above, the first time takes a long time (let's say 5 sec) while the second the query is cached so it's faster, (let's say 0.5 sec).

Thank you :)

[Adrian Schneider]

Have that all run as a cron... surely you wouldn't need all that as live data.

[cedivad]

vBulletin by default have the index for "dateline" already setted.

Ok, maybe i was wrong

mysql> ALTER TABLE `thread` ADD INDEX (`dateline`);
Query OK, 5236687 rows affected (4 min 41.92 sec)
Records: 5236687 Duplicates: 0 Warnings: 0

It now takes 0.5 sec else if it's not cached (i suppose). A great improvement :)

--------------- Added 1264021277 at 1264021277 ---------------

Have that all run as a cron... surely you wouldn't need all that as live data.

In fact i tought to do a static file cache... something like 16000 files of 0.4 mega each with the cache of every page of every category except the last one, that must be loaded "live". I think it's the best way ;)

[Adrian Schneider]

You could do it way smaller than that. If you are ordering in ascending order, all you'd need is to have all the ids associated for every page. The rest can be live. That shouldn't take up much space at all.