Speed up my Query please

[Swedie]

I have a quite large query to perform towards the vB database post table.

I want to pull out the last 10 posts, but I do not want to include certain threadid's and forumid's.

Right now the query is like this:
mysql_query("SELECT * FROM post WHERE threadid NOT IN($excludethreadid) AND forumid NOT IN($excludeforumid) LIMIT 10");
$excludethreadid is set with a bunch of threadids (into the hundreds, maybe toward a thousand).
$excludeforumid has a bunch of forumids not to be included in the query result.

Thing is that this query, when I have around 600+ users, is taking an extremely large amount of the CPU power and queing up requests and bogging down the whole system.

Is there a better way than my version above to query the post table and exclude certain preset id's in the two fields threadid and forumid?

Would appreciate help in this matter.

[Marco van Herwaarden]

I suppose that in the real query you also have a 'ORDER BY dateline DESC'?

You could try to add indexes to both forumid & dateline to speed things up.

[Andreas]

Just curious: post doesn't have a forumid column?

[amykhar]

Nope. It doesn't. Which is a royal pain in the patoot at times.

[Swedie]

Originally Posted by KirbyDE

Just curious: post doesn't have a forumid column?

I won't modify the database. But I do like to see a query that will do the job better than mine.

KirbyDE, yep. It is has a forumid field. Why?

Originally Posted by amykhar

Nope. It doesn't. Which is a royal pain in the patoot at times.

Don't come here being negative. Just kidding.

But are you sure? Because this query is really annoying me to the extent that I think I'm gonna remove it. But my users really want it because it helps users not having to browse through the forum all the time. Instead just visit the first page of my site.

btw, I guess you all understand that this is a vBulletin database that I am querying.

The number of posts is up into the millions.

[Andreas]

Normally it does not, that's why I asked.

[amykhar]

Swedie, what about caching? If it's such a large database, maybe you could create a cache for the last 10 posts. When somebody makes a new post, the cache is updated.

Amy

[Swedie]

Originally Posted by KirbyDE

Normally it does not, that's why I asked.

Oh shiete. You are actually right. Not sure what I was thinking.

Alright. To be sure I am quoting my own query right I'll post up the same verson i got online right now

PHP Code:

$sql = mysql_query("SELECT * FROM thread WHERE forumid NOT IN(3,6,11,12,19,20,24,28,29,31,32,34,35,36,37,38,40,41,43,44,45,48,49,50,52,53,58,61) ORDER BY lastpost DESC LIMIT 20"); 


Crap. I didn't have many right things in my first post.

I am querying the thread table.

Originally Posted by amykhar

Swedie, what about caching? If it's such a large database, maybe you could create a cache for the last 10 posts. When somebody makes a new post, the cache is updated.

Amy

hmm, that could definetly be one way of doing it. I'll see about modifying the vB code to suit my needs.

Currently I am caching the result in a .txt file on the server. And the script only runs every 30 seconds.

[Andreas]

PHP Code:

$sql = mysql_query("SELECT * FROM thread WHERE forumid NOT IN(3,6,11,12,19,20,24,28,29,31,32,34,35,36,37,38,40,41,43,44,45,48,49,50,52,53,5  8,61) AND visible IN (0,1,2) AND sticky IN (0,1) ORDER BY lastpost DESC LIMIT 20"); 


Except that and caching, I don't see any chances for optimization.

[The Geek]

Just thinking aloud... but what about putting in a dateline restriction that restricts the data to within the last week (that is assuming that you would have more than 20 within a week)?
I honestly dont know if that would help - just thinking aloud.