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]

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?

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]

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

$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,4 0,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.

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]

$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,4 0,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.

[Swedie]

$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,4 0,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.

Thanks. I guess I'll cry alone now.

Btw, totally off-topic, is this automerge thingy that has happened for me a couple of times. Is this a new feature in vb3.0.8? I have vb3.0.3 right now.

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.

Worth a shot I guess. :) thanks

[Andreas]

Nope. It's a Hack by Xenon :)

[calorie]

What about the order of the WHERE clause? For example, the first post has NOT IN($excludethreadid) AND forumid NOT IN($excludeforumid) but $excludethreadid is likely bigger than $excludeforumid, so does order matter here?

[Swedie]

Nope. It's a Hack by Xenon :)

Hack of the millenium!

What about the order of the WHERE clause? For example, the first post has NOT IN($excludethreadid) AND forumid NOT IN($excludeforumid) but $excludethreadid is likely bigger than $excludeforumid, so does order matter here?

Actually what I said in my first post differs to what I got live on the server at the moment. Right now i am excluding threadids inside the while() using "in_array" (or not).
I have a limit of 20 in the first query to ensure that the total after the exceptions has been made that i still have atleast 10 left.

[Andreas]

I am not sure if order does matter. But if mySQL is smart (I expect it is) it should optimize the query itself to perform bolean shortcut tests as fast as possible.

[Andreas]

I am not sure if order does matter. But if mySQL is smart (I expect it is) it should optimize the query itself to perform bolean shortcut tests as fast as possible.


Right now i am excluding threadids inside the while() using "in_array" (or not).

Array Operations are costly ...

Maybe (not sure!) better

if (strpos(',1,2,3,4,5,6,7,8,9,10,', ",$threadid,") !== false)
{
// got some valid data
}

[Marco van Herwaarden]

$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,4 0,41,43,44,45,48,49,50,52,53,58,61)AND visible IN (0,1,2)
AND sticky IN (0,1)
ORDER BY lastpost DESC LIMIT 20");
I don't understand why you select on visible and sticky, seems to me you are allowing all possible values. Take those out would speed up.
I suggest you just put back the threadid in the WHERE clause.

Then add indexes on all (on forumid, on threadid & on lastpost) columns used in a WHERE or ORDER BY clause.

This could speed up things a lot. For threadid and forumid, you can use unique indexes.

[Andreas]

Yes, those are all possible Values for sticky and visible.
Depending on the mySQL Version, this is necessary to convince the optimizer to use the Indexes.
Take a look at search.php, it does the same.

https://vborg.vbsupport.ru/showthread.php?t=66578