Speed up my Query please

[Swedie]

Originally Posted by KirbyDE

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.

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.

Originally Posted by 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.

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]

Originally Posted by KirbyDE

Nope. It's a Hack by Xenon

Hack of the millenium!

Originally Posted by 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?

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]

Originally Posted by KirbyDE

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

PHP Code:

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


[Marco van Herwaarden]

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)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