Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools Display Modes
  #1  
Old 04-25-2006, 11:11 AM
magicfox magicfox is offline
 
Join Date: Jul 2003
Posts: 15
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default SQL Query on big forum: Could this cause the server to crash?

Hi there!

I want to use the "PluhNews" news grabber for a updated version of my general homepage to include the latest 8 threads from my vBulletin 2.3.0 forum directly on the main page, but I'm afraid the resulting server load could be too big, when "PluhNews" performs the collection of the latest threads.

"PluhNews" uses the following SQL query to grab the latest entries from the database:

Code:
SELECT threadid, title, forumid, replycount, postusername, postuserid, lastposter, dateline, iconid FROM thread WHERE forumid IN ("8,9,10,11,12,13,14,15,16,17,18,24,33") ORDER BY threadid DESC LIMIT 8
8,9,10 and so on are the forums which should be "scanned".

So each time a user calls my homepage, this query will be started. My forum is pretty big (~35.000 postings each month, ~300 users at once online, whole database ~700 MB) and I'm not sure if the server could stand this query.

Since I'm not familiar with SQL at all my questions:

- Do you think this query could cause problems or is the query not server intensive at all?
- Is there a way to change to query for a lower impact on the server?

Thanks a lot!
Reply With Quote
  #2  
Old 04-25-2006, 05:52 PM
Gio~Logist's Avatar
Gio~Logist Gio~Logist is offline
 
Join Date: Jun 2004
Location: San Francisco
Posts: 2,575
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by magicfox
Hi there!

I want to use the "PluhNews" news grabber for a updated version of my general homepage to include the latest 8 threads from my vBulletin 2.3.0 forum directly on the main page, but I'm afraid the resulting server load could be too big, when "PluhNews" performs the collection of the latest threads.

"PluhNews" uses the following SQL query to grab the latest entries from the database:

Code:
SELECT threadid, title, forumid, replycount, postusername, postuserid, lastposter, dateline, iconid FROM thread WHERE forumid IN ("8,9,10,11,12,13,14,15,16,17,18,24,33") ORDER BY threadid DESC LIMIT 8
8,9,10 and so on are the forums which should be "scanned".

So each time a user calls my homepage, this query will be started. My forum is pretty big (~35.000 postings each month, ~300 users at once online, whole database ~700 MB) and I'm not sure if the server could stand this query.

Since I'm not familiar with SQL at all my questions:

- Do you think this query could cause problems or is the query not server intensive at all?
- Is there a way to change to query for a lower impact on the server?

Thanks a lot!
That query seems fine to me and does not look as if it would take a long time to generate.
Reply With Quote
  #3  
Old 04-25-2006, 06:00 PM
magicfox magicfox is offline
 
Join Date: Jul 2003
Posts: 15
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Thanks for the reply, gio~logist!

Do you think the server can stand this query, even if called multiple times at once? When two, three or even more visitors at once are visiting my homepage and forcing the query to catch the informations from the database?

I wonder because my database is ~700 MB big and the query needs to search the whole database, or not?
Reply With Quote
  #4  
Old 04-25-2006, 06:04 PM
Paul M's Avatar
Paul M Paul M is offline
 
Join Date: Sep 2004
Location: Nottingham, UK
Posts: 23,748
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

The normal operation of vb runs more intensive queries than that. I see no reason to worry.
Reply With Quote
  #5  
Old 04-25-2006, 09:29 PM
filburt1 filburt1 is offline
 
Join Date: Feb 2002
Location: Maryland, US
Posts: 6,144
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Don't use quotes in the IN clause.
Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 01:37 PM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.07089 seconds
  • Memory Usage 2,203KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (2)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (5)post_thanks_box
  • (5)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (5)post_thanks_postbit_info
  • (5)postbit
  • (5)postbit_onlinestatus
  • (5)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete