The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
||||
|
||||
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 -------------------------------------------------------------------------------- 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")."\");"); } |
#2
|
|||
|
|||
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:
|
#3
|
||||
|
||||
Well, I guess I am going to have to remember that.
[high]* Link14716 bookmarks for reference. [/high] Thanks. |
#4
|
||||
|
||||
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?
|
#5
|
||||
|
||||
couldn't you do
PHP Code:
|
#6
|
||||
|
||||
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. |
#7
|
||||
|
||||
well foo there goes all my mysql knowledge out the window
|
#8
|
||||
|
||||
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.
|
#9
|
||||
|
||||
^^BuMp^^
|
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|