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 06-26-2005, 04:30 PM
HakkieDEV HakkieDEV is offline
 
Join Date: Jun 2004
Posts: 62
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default top10stats in 1 query?

Hi all,

I've some top10 statistics on my index.php, in total 5 different ones.

To make it a bit easier, lets call them stats1, stats2, stats3, stats4, stats5.

This means that a query has to be run 5 times, to select the data, then sort them.

I was wondering if something like this could be done in a cronjob (lets say once a day) and that the results of those 5 querys would be inserted in a new table.

The newtable would be something like:
stats1_userid, stats1_username, stats1_posts, stats2_userid, stats2_username, stats2_posts, etc, etc.

While doing this, I could save 4 querys on my index.php, wich is heavily modded and I am desperate to look for something that could be done with less querys.

Is this possible? if yes, how?
Reply With Quote
  #2  
Old 06-26-2005, 04:59 PM
Xenon's Avatar
Xenon Xenon is offline
 
Join Date: Oct 2001
Location: Bavaria
Posts: 12,878
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

if you are using vb3 you can use the vb cron to fetch the needed data, then put everything into an php array, serialize that and put it into the datastore.
the vb datastore can be fetched without adding extra queries at all ($specialtemplates )
Reply With Quote
  #3  
Old 06-26-2005, 09:50 PM
HakkieDEV HakkieDEV is offline
 
Join Date: Jun 2004
Posts: 62
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Okay, I figured out a way, but I don't understand that the page isn't showing faster.


OLD-------------------------------------------------------
Page generated in 4.02501 seconds with 43 queries [Server Loads: 0.08 0.07 : 0.15]
OLD-------------------------------------------------------



NEW-------------------------------------------------------
Page generated in 4.06289 seconds with 39 queries [Server Loads: 0.14 0.10 : 0.15]
NEW-------------------------------------------------------

Is it because the new table has 75 rows?

Any idea how I could get around this?
Reply With Quote
  #4  
Old 06-27-2005, 09:59 AM
Xenon's Avatar
Xenon Xenon is offline
 
Join Date: Oct 2001
Location: Bavaria
Posts: 12,878
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

i think there may be something else which takes too much time
Reply With Quote
  #5  
Old 06-27-2005, 02:57 PM
HakkieDEV HakkieDEV is offline
 
Join Date: Jun 2004
Posts: 62
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

So, 1 query that has to search in 75 rows is normally faster then 5 different querys to do the 75 rows?
Reply With Quote
  #6  
Old 06-27-2005, 03:26 PM
Xenon's Avatar
Xenon Xenon is offline
 
Join Date: Oct 2001
Location: Bavaria
Posts: 12,878
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

one query doing 75 rows shouldn't take 4 secounds at all
Reply With Quote
  #7  
Old 06-27-2005, 03:53 PM
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Posts: 25,415
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Well the number of returning rows don't always say something about the number of rows MySQL has to process, select, join and sort, before it can return that number of rows.

A select with a '...FROM singletable WHERE column = 1' that would select 75 out of the 150 rows, would be very fast.

Selecting from multiple big tables, joining on nonunique fields, sorting, etc, still returning 75 rows could be very time consuming.
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 12:41 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.05451 seconds
  • Memory Usage 2,214KB
  • 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
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (7)post_thanks_box
  • (7)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (7)post_thanks_postbit_info
  • (7)postbit
  • (7)postbit_onlinestatus
  • (7)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