vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Optimization Needed (https://vborg.vbsupport.ru/showthread.php?t=47186)

Link14716 12-31-2002 09:19 PM
Optimization Needed
 
I created a script to manage cheat codes for my gaming site, but to count the codes per game, it adds a query per game. Example:

Code:
--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS codes FROM post WHERE site='2' && threadid='176'
Time before: 0.32487499713898
Time after:  0.32634603977203

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS codes FROM post WHERE site='2' && threadid='177'
Time before: 0.32819998264313
Time after:  0.32953000068665

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS codes FROM post WHERE site='2' && threadid='138'
Time before: 0.33139204978943
Time after:  0.3329199552536

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS codes FROM post WHERE site='2' && threadid='369'
Time before: 0.33477103710175
Time after:  0.33609294891357

--------------------------------------------------------------------------------
Now, it's not a big deal now, being such a small site, but I wouild like to see how I could optimize the code. Here is the complete action taken from the script:

Code:
// START GAME LIST
if ($do=="glist") {

$alphaparent=$DB_site->query_first("SELECT * FROM forum WHERE forumid='$alphaid' ORDER BY displayorder ASC");
$sysname=$DB_site->query_first("SELECT * FROM forum WHERE forumid='$alphaparent[parentid]' ORDER BY displayorder ASC");
$sysname=$sysname[title];
$sysletter=$alphaparent[title];

$typequery=$DB_site->query("SELECT * FROM thread WHERE forumid='$alphaid' ORDER BY title ASC LIMIT 100");
while ($glist=$DB_site->fetch_array($typequery)) {

$gname=$glist[title];
$gid=$glist[threadid];

$countjokes2=$DB_site->query_first("SELECT COUNT(*) AS codes FROM post WHERE site='2' && threadid='$gid'");
$totalgcodes=number_format($countjokes2['codes']);

if ($totalgcodes=="1") {
$c="code";
} else {
$c="codes";
}

  eval("\$gbit .= \"".gettemplate("site_v$v" . "_codes_gbits")."\";");
}
eval("dooutput(\"".gettemplate("site_v$v" . "_codes_glistdisplay")."\");");
}
Thanks in advance. :)

futureal 01-01-2003 12:10 AM
It sounds like you would be better off just grabbing all of the threadids from the post table and then counting those using a PHP loop, without any additional queries.

Something like:

PHP Code:
$allthreads = $DB_site->query("SELECT threadid FROM post WHERE site='2'");

while ($eachthread = $DB_site->fetch_array($allthreads))
  $thread_table[$eachthread[threadid]]++;

foreach ($thread_table as $k => $v)
  echo "threadid $k has $v entries<br>"; 
That would output a list of how many posts are in each thread that matches site=2. Basically, the same thing that your original code did, but with a single query.

Link14716 01-01-2003 09:16 PM
Well, I guess I am going to have to remember that.

[high]* Link14716 bookmarks for reference.
[/high]

Thanks. :)

Link14716 01-22-2003 02:07 AM
I can't find a good way to put that into the above action. It keeps repeating things, and I still have no knowlege of the correct syntax/placement to make it where that query is only executed once, doesn't keep repeating other things, doesn't keep repeating other queries, and actually works. Can someone help me? :)

mr e 01-22-2003 02:17 AM
couldn't you do

PHP Code:
SELECT count(*) AS codes176, count(*) AS codes177, count(*) AS codes138, count(*) AS codes369 FROM post WHERE site='2' && threadid='176', WHERE site='2' && threadid='177', WHERE site='2' && threadid='138', WHERE site='2' && threadid='369' 
or can you not do that, i dunno

Link14716 01-22-2003 02:21 AM
Nope, becuase that'd kill the whole purpose of the threads being dynamic. It'd require me to add 1,000 or so things to one query, and a concious effort to do so. But hey, thanks anyways ;)

Also, there are 36 threads to deal with now. So, that'd be a long query, and not only would it be hard to manage and unnecessary. I know there is a better way, and if I could get that loop futureal posted to not be looped itself by the fetch_array, then it should all work :)

EDIT: Also, that would work if I were dealing with static HTML basically, but that is not the case this time. :)

mr e 01-22-2003 02:32 AM
well foo there goes all my mysql knowledge out the window :rolleyes:

Link14716 01-22-2003 02:35 AM
lol, don't worry, it would be useful, but since the threadid's are dynamic and they all must be running on the same variable, it would not be suitable for my problem. My way still works, but sooner or later there will be 40-50 queries with a lot of people viewing the page, and I want to prevent that from happening. :)

Link14716 01-31-2003 09:30 PM
^^BuMp^^


All times are GMT. The time now is 10:03 AM.

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.01116 seconds
  • Memory Usage 1,739KB
  • 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
  • (2)bbcode_code_printable
  • (2)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (9)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