Serious Query Problem

[TheSaint-AeD]

I have a serious Problem with the Querries in showthread.php. Everytime I post in an Thread, next time I open the thread it needs exactly 2 Querries more. As I have 25 Threads / Page this means 50 additional Querries on a full Page! As I already have about 25 Querries as default, i got between 30-80 Querries in a Thread :(

Can anybody explain to my why this happens and why other users on my board don't seem to add so many querries per post? I have access-masks enabled and Quick-Reply + Lesanes Award-System + Quick Delete installed.

[Xenon]

it seems you have a query call within a while loop

open admin/functions.php
got to function getpostbit

if you find any query in this function, you should take it out.

[TheSaint-AeD]

Found this

if ($post[award]=="1")
{
$result=$DB_site->query("SELECT * FROM useraward WHERE userid='$post[userid]' AND awardsid=aw ");
while ($awardsp=$DB_site->fetch_array($result))
{
eval("\$post[hasaward] .= \"".gettemplate("awards")."\";");

}
}

This is the only query performed in getpostbit, may there be some messed codes in Lesanes Award-Hack? Me and some other Useres got even more Problems with it. Sometimes donating an Award doubles all DB-Entries which is only shown in user.php but not in awards.php.

[Xenon]

ok this is the problem which produces the high query ammount.

you should read the supportthread of lesanes hack, maybe there is a new method to save this query.

if this query couldn't be saved without changen a larg ammount of code, maybe a cache can help.

you should suggest that ;)

[TheSaint-AeD]

Thx 4 your help, Xenon, but I don't know enough about PHP + MySQL to solve this Problem myself. I read the whole Support Thread (which is quite long ;)) 3 Times because I couldn't believe noone else notices the Problems with the Hacks, but it seems, as if I was the only one.

I don't think there's gonna be a fix of Lesane, as he already had to give up his "Tribute to 2Pac" Board due to his lack of Time. Could anybody else of the vb.org-Staff take care of that Thread? Missing the Award-Hack would be missing one of the most ruling Features of vB ;)

[Lesane]

You can't take out that query. It's just a query wich is needed to get the awards from an user.

I'm no supporter for while loops in the posbit as well. You could move the awards to the userinfo page.

How many awards do you have?

[TheSaint-AeD]

I've got about 20 Awards in total and an average of 1-2 per User. Due to the other Problem (Awards get Duplicated when giving to certain users) I deleted all Awards, but still the same Problem.

Man, Lesane, that was fast :) . Are you the Neighbour of Xenon? *G*

[Lesane]

A new version of the awards hack is coming within a few weeks. The new version will have no query's in the postbit anymore.

First i am going to finish the new version of the store hack. :p

[TheSaint-AeD]

I'll be looking nervously forward to it, refreshing vb.org every 10 minutes ^^ Btw, Lesane: You had a great board, I'm missing it already though I had only few time to visit it.

[Xenon]

ahh, good to hear lesane ;)

@Saint: hmm, you can try this, it will save at least some of the queries if you have more posts of the same user on one page:

change the postbit code to this:
if ($post[award]=="1")
{
if(isempyt($awardresultcache[$post['userid']]) $awardresultcache[$post['userid']]=$DB_site->query("SELECT * FROM useraward WHERE userid='$post[userid]' AND awardsid=aw ");
while ($awardsp=$DB_site->fetch_array($awardresultcache[$post['userid']]))
{
eval("\$post[hasaward] .= \"".gettemplate("awards")."\";");

}
}

then in getpostbitfunction after
global $bbuserinfo,$session,$ignore,$cookietimeout;

add this:
static $awardresultcache


i think this should reduce the query ammount

[TheSaint-AeD]

I already deinstalled the Hack and went back to less than 40 Querries in showthread :) .

@ Xenon: Danke trotzdem vielmals f?r die schnelle und kompetente Hilfe ;)

[Xenon]

you're welcome

hmm, du solltest nicht ?ber 35 kommen bei showthread, unter 30 w?re noch besser ;)

[Erwin]

Originally posted by TheSaint-AeD
I already deinstalled the Hack and went back to less than 40 Querries in showthread :) .

40 queries is still significant. :) Try to reduce it a bit more - ideally to 25 or less, since showthread is the most commonly accessed page on a forum.

[TheSaint-AeD]

hrhr :mad:

I know that ~35 Querries is a lot on showthread, but my Servers are good enough to face this, and I don't wanna miss any of the Features causing these Querries. Visit me on http://foren.gslallianz.de if you want ;) Still a lot under Construction, but already some interessting things to see ;)

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

Xenon, there seems to be another Query-Loop. Seems to have something to do with the Quick-Delete Post Hack, I'm investigating atm. and post the Results as soon as possible.

[Xenon]

yes this is important to put away :)

don't use queries in loops :).

most of the time these queries can be avoided with good joined queries.... but not always :(

[TheSaint-AeD]

wtf, this is getting weird... ok, here we are:


Thread has 1 Post, User is Moderator:

Seite generiert in 0.20259094 Sekunden (74.67% PHP - 25.33% MySQL) mit 33 queries.


Same Thread, User is no Mod:

Seite generiert in 0.18163002 Sekunden (71.52% PHP - 28.48% MySQL) mit 35 queries.


Now we are 10 Posts later...

User is Moderator:

Seite generiert in 0.38245904 Sekunden (84.50% PHP - 15.50% MySQL) mit 42 queries.


In Comparison to that, same Thread, no Mod:

Seite generiert in 0.36550105 Sekunden (82.41% PHP - 17.59% MySQL) mit 53 queries.



Moderators get +1 Query / Post, Non-Moderators even +2! There was I time I was running the Award-Hack aswell as the Quick-Delete-Post Hack and had 40 Posts / Page. This means, my Users must have been at 150+ Querries!!! I'm wondering how my Server got through this, with my Board having ~150 highly active Users in average / day.

[Xenon]

yes, that's really a problem :)

somewhere in your whileloop ther is one or two queries too much.

carefully recheck the getpostbit function and the loop in showthread.php where getpostbit is called

[TheSaint-AeD]

k, removing the hack cleared it out. I got no more query-loops and I'm back at unbelievable 32 querries performed in Showthread. Here is the wrong Code:

if ($action=="deletethispost") {
$firstpost=$DB_site->query_first("SELECT postid,dateline FROM post WHERE threadid='$threadid' ORDER BY dateline LIMIT 1");
if ($postid==$firstpost[postid]) {
if ($getperms[candeletethread]) {
deletethread($threadinfo[threadid],$foruminfo[countposts]);
updateforumcount($threadinfo[forumid]);

$url="forumdisplay.php?s=$session[sessionhash]&forumid=$threadinfo[forumid]";
eval("standardredirect(\"".gettemplate("redirect_deletethread")."\",\"$url\");");
} else {
show_nopermission();
}

} else {
$foruminfo=getforuminfo($threadinfo[forumid]);
deletepost($postid,$foruminfo[countposts],$threadinfo[threadid]);

updatethreadcount($threadinfo[threadid]);
updateforumcount($threadinfo[forumid]);

$url="showthread.php?s=$session[sessionhash]&threadid=$threadinfo[threadid]";
eval("standardredirect(\"".gettemplate("redirect_deletepost")."\",\"$url\");");
}
}

I'm wondering why I was the first User noticing this, as neither the award-hack nor the quick-delete-post hack are that unpopulare. I sent Teck a PN with a Link to this Thread.

[TheSaint-AeD]

ok, TECK posted a Code which doesn't use Mod or no-Mod but Usergroup-ID's. Not as comfortable as the first Version, but 50 Querries less leave no Choice ;) Does anybody has an Idea for a fully working Code?

@ Xenon: Die Foren mal besucht? Wenn Dir was davon gef?llt (z. B. einer der Styles) einfach PN, dann schick ich Ihn Dir, ist das Wenigste was ich Dir anbieten kann. Danke nochmal f?r die Hilfe.

[Xenon]

i think TECK will come up with a comfortable and fully working code, i know him ;)

just have to wait ;)

Hmm, nur wenns wirklich keine umst?nde macht, der Galaktic Blue Style w?rde nach kleinen ?nderungen gut zu meinem Master of Orion3 unterforum passen :)
Meine Addy Xenon@ und dann die url von meinem Forum ;)
btw. dein Stylish green erinnert mich an was ;)

[TheSaint-AeD]

@ Xenon: Hab mich mal in Deinem Forum registriert, werde so schnell wie möglich daran arbeiten.