vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Speed up my Query please (https://vborg.vbsupport.ru/showthread.php?t=96251)

Swedie 09-14-2005 10:54 AM
Speed up my Query please
 
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 09-14-2005 11:01 AM
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 09-14-2005 11:04 AM
Just curious: post doesn't have a forumid column?

amykhar 09-14-2005 11:06 AM
Nope. It doesn't. Which is a royal pain in the patoot at times.

Swedie 09-14-2005 11:07 AM
Quote:
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?

Quote:
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 09-14-2005 11:11 AM
Normally it does not, that's why I asked.

amykhar 09-14-2005 11:17 AM
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 09-14-2005 11:17 AM
Quote:
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.

Quote:
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 09-14-2005 11:30 AM
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 09-14-2005 11:38 AM
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 09-14-2005 11:40 AM
Quote:
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.

Quote:
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 09-14-2005 11:41 AM
Nope. It's a Hack by Xenon :)

calorie 09-14-2005 11:46 AM
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 09-14-2005 11:48 AM
Quote:
Originally Posted by KirbyDE
Nope. It's a Hack by Xenon :)
Hack of the millenium!

Quote:
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 09-14-2005 11:51 AM
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 09-14-2005 11:54 AM
Quote:
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.
Quote:
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 09-14-2005 02:23 PM
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 09-14-2005 04:10 PM
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


All times are GMT. The time now is 02:49 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01240 seconds
  • Memory Usage 1,768KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (5)bbcode_php_printable
  • (10)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (18)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete