Slow queries for 'display threads' searches

[OmerE]

Hi,

I have quite a few slow queries taking place, of this form:

# Time: 051029 15:27:31
# User@Host: vb_user[vb_user] @ localhost []
# Query_time: 43  Lock_time: 0  Rows_sent: 67962  Rows_examined: 138668
SELECT post.postid, post.threadid
        FROM post AS post
        INNER JOIN thread AS thread ON(thread.threadid = post.threadid)
        LEFT JOIN deletionlog AS delpost ON(delpost.primaryid = post.postid AND delpost.type = 'post')
        WHERE post.postid IN(3,10,28,56,57,73,79,98,103.....)
        AND delpost.primaryid IS NULL
        AND post.visible = 1;

Now I realize those are performed to display the threads to which these posts belong, but is there no solution to the huge number of rows the query has to go to?

133,000 rows out of around 750,000 is quite a lot, and those queries are really killing my server (which is a bit underpowered, granted).

Ideas, anyone?

Regards,
Omer

[Steve Machol]

Please see this thread for help with optimizing your server:

http://www.vbulletin.com/forum/showthread.php?threadid=70117

Then post the requested info in a new thread in that forum.

[OmerE]

Hi Steve,

Thank you for your reply.

However, this is not a request for server optimization, but rather an issue with vB itself and that specific query. Regardless of the server state, a search query that has to sort through 133,000 is very intense.

I was hoping perhaps someone with a very active board has optimized this query somehow, as I personally don't see how to go about doing that.

Regards,
Omer

[Marco van Herwaarden]

If diskspace is not an issue, you could try adding another non-unique index on the 'threadid' column of the post table.

What MySQL version are you running?

[Baldy]

If diskspace is not an issue, you could try adding another non-unique index on the 'threadid' column of the post table.

We had the same problem with that query bringing our server to its knees. We have over 2,000,000 posts and when someone would search for a common 4-letter word like "long", it could send us into a minutes-long tailspin that could cause timeout errors.

Yes, we did all the server optimization things. But when the query looks like:

SELECT thread.threadid , post.postid
FROM thread AS thread
INNER JOIN post AS post ON(thread.threadid = post.threadid)
WHERE post.postid IN(78,137,182,235,253,277,356,357,449,483,594 ...

it has the potential to be really slow.

We followed MarcoH64's advice to add the following index:

CREATE INDEX threadpost ON post (postid,threadid);

So far, so good. It seems to have made a world of difference.

Baldy