Can someone please help me optimize this query as it happens all the time.

These queries appears at forum ID 96 I have there like 164,833 threads and 418,405 posts. And I can't figure out what is wrong.

# User@Host: forums[forums] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 100 Rows_examined: 116077
SELECT threadid , title, lastpost, replycount
FROM thread AS thread
WHERE forumid = 96
AND visible = 1
AND open <> 10

ORDER BY dateline ASC
LIMIT 5000,100;
# User@Host: forums[forums] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 100 Rows_examined: 115777
SELECT threadid , title, lastpost, replycount
FROM thread AS thread
WHERE forumid = 96
AND visible = 1
AND open <> 10

ORDER BY dateline ASC
LIMIT 4700,100;
# Time: 090208 19:52:25
# User@Host: forums[forums] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 100 Rows_examined: 116177
SELECT threadid , title, lastpost, replycount
FROM thread AS thread
WHERE forumid = 96
AND visible = 1
AND open <> 10

ORDER BY dateline ASC
LIMIT 5100,100;
# Time: 090208 19:52:26
# User@Host: forums[forums] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 100 Rows_examined: 116377
SELECT threadid , title, lastpost, replycount
FROM thread AS thread
WHERE forumid = 96
AND visible = 1
AND open <> 10

ORDER BY dateline ASC
LIMIT 5300,100;

i've refrained answering this till now, as i'm not sure what problem here.

In my experience i can say the problem for most mysql optimization problems in large DBs are bad structure databses, specific the lack of INDEX/KEY fields, for fields that are used in WHERE statements.
Solution: adding those fields INDEX.
This will make any large databses search matter of mili-seconds, while without them queries can take on large DB's even closer to 20-30 secons (on DB with ovr 3 milllions records in it).

Did this start after installing something specific you can remember ?
Also high cpu usage can affect greatly on mysql performance, so if this is dedicated/vps box, try to check if anything is overloading your box cpu.

Thanks for your reply Idan!

And no I did not install anything. I have those slow queries at forum ID 96.

Only vBulletin running on the server.

It's a dedicated dual xeon 3.0 with 8 GB RAM RAID 1.

I wonder if there's a lack of index keys I would of have slow queries on other foru ID'S

Forum is running quite fast and suddenly load goes even to 20

Any suggestions would be appreciated.

Allen

In general, if certain forum id has more posts, then it means the sql query will have to examine more rows. should the WHERE include non indexed field(s), the search time can be rather long & spike cpu during process.

looking at my current indexes list on 'thread' table (on my server) i see that "open" field is NOT indexed (by default). That could explain why any query with "open" in WHERE condition could have slower queries on large databases. Try to run query without the "AND open <> 10" in it, see if this is fast for you, or still slower for you.
If the above is fast for you, then possible solution SUGGESTION is making the field "open" (on 'thread' table) indexed.

DISCLAIMER:
The above suggestion is given as my advise to you, from my own knowledge, AS-IS & should it be attempted to be made, it's totaly upon your decision. I, nor this site, cannot take any responsibility for any result of such action. You should FULLY backup your entire database before any attempt to do any database operation, should it fail & result in "crash" that will force recovery from last backup.
Please be aware that during this process can sometimes (depands on cpu) take up to several hours, during which the cpu is very high, and box likely not to be useable during this time.
Furthermore i would highly advise take site offline during this time, so there wont be any new DB additions during the process, in way that might conflict/fail the index process.

About the disclaimer it's ok I cloned the the website on another server so I'm testing in there.

So if you have any queries to run shoot it.

I tried with and without AND open <> 10 and it's taking 1.+ seconds for the query to finish.
With AND open <> 10

Showing rows 0 - 99 (100 total, Query took 1.0899 sec)

Whithout AND open <> 10

Showing rows 0 - 99 (100 total, Query took 1.0838 sec)

This is quite fast. I have 3+ sec everytime when I click on the link on forumdisplay 96 as admin "results from microstats."

If you have other ideas I can run queries as I'm testing this on backup server.

Allen

i'm sorry, but i have no further ideas at this time. I'f i'll think on something i'll post it here.