Optimization Needed

[Link14716]

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:

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

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:


// 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]

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:


$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]

Well, I guess I am going to have to remember that.

* Link14716 bookmarks for reference.

Thanks. :)

[Link14716]

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]

couldn't you do


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]

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]

well foo there goes all my mysql knowledge out the window :rolleyes:

[Link14716]

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]

^^BuMp^^