Post table far too large

[mxtabs]

Hi all,

I run a large board in which the post table has grown to > 6GB. This is causing vbulletin to operate enormously slow, and the slow query log is filled with items like this:

# Time: 080701 21:40:06
# User@Host: root[root] @ localhost []
# Query_time: 61 Lock_time: 0 Rows_sent: 3 Rows_examined: 61123
SELECT post.postid
FROM post AS post

WHERE post.threadid = 167837
AND post.visible = 1


ORDER BY post.dateline
LIMIT 61120, 20;




I'm guessing this is a result of the massive indices which have to be searched. Is there any way around this, or will I have to delete old posts?

[Andy Huang]

It looks like an isolated issue where you have over 60k posts in a single thread. Most of the delay comes from the sorting part, where it needs to sort through (in this particular case) 60k+ posts based on date before it can present the information. While computers are pretty good at sorting, it would still take some time to sort that many records. I'd recommend you to close / delete that thread and let things phase out should ease off the loads. Alternatively, just move it to a private section where no one (or very limited people) can have access to it, so it doesn't cause problems.

[mxtabs]

Any idea what this is?

# Time: 080701 22:16:08
# User@Host: root[root] @ localhost []
# Query_time: 73 Lock_time: 0 Rows_sent: 45599891 Rows_examined: 45599891
use vb_fresh2;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `postindex`;

[Andy Huang]

Post index is for searching... I don't know why it is litterally searching for EVERYTHING on that table though... vBulletin does not query for that by default, an addon may be modifying your queries or doing strange strange things with your site's search.