View Full Version : Help with a query!
Query: SELECT usergroupid FROM user WHERE userid=1
Time before: 0.49647402763367
Time after: 0.49692595005035
EEK!
This SELECT usergroupid FROM user WHERE userid=1 query shows up on full page of a thread on showthread about 12 times!
I have 42 queries on the page, and I knew something was wrong, just haven't had time to check for a while, just did and it's this thing...maybe looping?
How could I solve this?
NTLDR
12-11-2002, 08:30 PM
Check the buildpostbit function in admin/functions.php and see if you have any queries in the function.
Xenon
12-11-2002, 09:37 PM
the function is called getpostbit ;)
and yes, there shouldn't be a query in this function, because it will add an extra query PER POST.
if you need the usergroupid because of a hack, you should use a JOIN query in showthread instead..
NTLDR
12-12-2002, 01:23 PM
I was thinking of the comment thats above it :) I guess thats what it used to be called.
I just looked in the whole postbit part of functions.php and found no query that could be causing this...
Could you maybe give me ideas as to what the query is? How I could locate it?
And do all queries have DB_site before them?
NTLDR
12-12-2002, 03:40 PM
It would either be in the postbit function in admin/functions.php or in showthread.php (do you get this query lots of times on other pages?)
It would look something like:
$DB_site->query_first("SELECT usergroupid FROM user WHERE userid=$bbuserinfo[usergroupid]");
Perhaps something in global.php or the phpinclude template contains this in a loop? (If its on pages other than showthread too).
It's not on forumhome or forumdisplay for sure, and I don't think it's anywhere else, I mean it appears around 10 times on showthread! It can't be on forumhome as forumhome is only 22 queries, and forumdisplay is only 25 (gotta optimize this actually)...
I'll have to look again for the query.
if ($usergroupid==-1 or $usergroupid==0) {
if ($userid==0) {
$usergroupid=1;
} else {
if (isset($usercache["$userid"])) {
$usergroupid=$usercache["$userid"]['usergroupid'];
} else {
$getuser=$DB_site->query_first("SELECT usergroupid FROM user WHERE userid=$userid");
$usergroupid=$getuser['usergroupid'];
}
}
}
That's in functions.php, in the getpermissions part, it's the only part where there is a SELECT usergroupid query..
NTLDR
12-12-2002, 04:42 PM
Thats probably supposed to be there, is the query on all showthread pages? If not is it threads in private forums or forums with specific usergroup permissions?
NTLDR
12-12-2002, 04:44 PM
Yep, thats definatly suposed to be there. Do you have anything like:
$permissions=getpermissions($forumid);
In the getpostbit function? It may be repeatedly querying your permissions.
I don't have that...nope...
if statements don't have anything to do with queries right? I have a lot of custom if statements in functions.php...
NTLDR
12-12-2002, 05:04 PM
No, the only thinks that could be generating querys in it are queries ($DB_site->, mysql_query) or calls to other functions that may contain queries.
Then maybe I should look in showthread.php for some sort of call to a function which could be generating loads of queries?
Query: SELECT usergroupid FROM user
But what I don't understand is, this is selecting the usergroupid from the user table, any ideas why?
NTLDR
12-12-2002, 05:11 PM
I have that query once on a unhacked showthread, I would say that its getting your usergroupid to check your permissions for view the thread or the other functions on the that page.
What's making it come up 10 times then?
NTLDR
12-12-2002, 05:15 PM
How many times do you have: getpermissions in showthread.php? It should be there twice, once in showpost and once in showthread
Well I searched functions.php once again and I found the query in the getpermissions area:
/ ###################### Start getpermissions #######################
function getpermissions($forumid=0,$userid=-1,$usergroupid=-1,$parentlist="") {
// gets permissions, depending on given userid and forumid
global $DB_site, $usercache, $bbuserinfo, $enableaccess;
static $permscache, $usergroupcache;
$userid=intval($userid);
if ($userid==-1) {
$userid=$bbuserinfo['userid'];
$usergroupid=$bbuserinfo['usergroupid'];
}
if ($usergroupid==-1 or $usergroupid==0) {
if ($userid==0) {
$usergroupid=1;
} else {
if (isset($usercache["$userid"])) {
$usergroupid=$usercache["$userid"]['usergroupid'];
} else {
$getuser=$DB_site->query_first("SELECT usergroupid FROM user WHERE userid=$userid");
$usergroupid=$getuser['usergroupid'];
}
}
}
if (!isset($permscache["$usergroupid"]["$forumid"])) {
if (!$forumid) {
if (!isset($usergroupcache["$usergroupid"])) {
$usergroupcache["$usergroupid"] = $DB_site->query_first("SELECT * FROM usergroup WHERE usergroupid=$usergroupid");
return $usergroupcache["$usergroupid"];
} else {
return $usergroupcache["$usergroupid"];
}
} else {
if (!$parentlist) {
$parentlist=getforumarray($forumid);
}
$forums=getforumlist($forumid,"forumid","OR",$parentlist);
if ($enableaccess==1 AND $access=$DB_site->query_first("SELECT *,INSTR(',$parentlist,', CONCAT(',', forumid, ',') ) AS ordercontrol FROM access WHERE userid=$userid AND $forums ORDER BY ordercontrol LIMIT 1")) {
if ($access['accessmask']==1) {
if (!isset($usergroupcache["$usergroupid"])) {
$getperms=$DB_site->query_first("SELECT * FROM usergroup WHERE usergroupid=$usergroupid");
$usergroupcache["$usergroupid"] = $getperms;
} else {
$getperms = $usergroupcache["$usergroupid"];
}
} else {
if (!isset($usergroupcache["$usergroupid"])) {
$getperms2=$DB_site->query_first("SELECT * FROM usergroup WHERE usergroupid=$usergroupid");
$usergroupcache["$usergroupid"] = $getperms2;
} else {
$getperms2 = $usergroupcache["$usergroupid"];
}
while ( list($gpkey,$gpval)=each($getperms2) ) {
$getperms["$gpkey"] = 0;
}
}
} else if (!$getperms=$DB_site->query_first("SELECT *,INSTR(',$parentlist,', CONCAT(',', forumid, ',') ) AS ordercontrol FROM forumpermission WHERE usergroupid=$usergroupid AND $forums ORDER BY ordercontrol LIMIT 1")) {
if (!isset($usergroupcache["$usergroupid"])) {
$getperms=$DB_site->query_first("SELECT * FROM usergroup WHERE usergroupid=$usergroupid");
$usergroupcache["$usergroupid"] = $getperms;
} else {
$getperms = $usergroupcache["$usergroupid"];
}
}
}
$permscache["$usergroupid"]["$forumid"]=$getperms;
} else {
return $permscache["$usergroupid"]["$forumid"];
}
return $getperms;
}
That's the only place that kind of query shows up in functions.php
Originally posted by NTLDR
How many times do you have: getpermissions in showthread.php? It should be there twice, once in showpost and once in showthread
// Logician Download Thread Hack
if ($action=="download") {
require("./global.php");
if (!$threadid OR $threadid<1) {show_nopermission();}
$thread_db=$DB_site->query_first("SELECT * from thread WHERE threadid=$threadid AND visible=1");
if (!$thread_db[threadid] OR $thread_db[threadid]<0) {show_nopermission();}
$forum=getforuminfo($thread_db['forumid']);
// Check Permissions so that a smartass wouldnt download a thread he doesnt allowed
$getperms=getpermissions($thread_db['forumid'],-1,-1,$forum['parentlist']);
if (!$getperms['canview']) {show_nopermission();}
if (!$getperms['canviewothers'] and ($thread['postuserid']!=$bbuserinfo['userid'] or $bbuserinfo['userid']==0)) {show_nopermission();}
That code is near the top of showthread.php (there is a getpermissions call in there).
It's the download thread hack from logician...
NTLDR
12-12-2002, 05:48 PM
That should only add one query at the most to the page.
Could I PM you my showthread.php and functions.php?
NTLDR
12-12-2002, 07:10 PM
Sure, I can't promise anything but I should have a few mins too take a look :)
vBulletin® v3.8.12 by vBS, Copyright ©2000-2025, vBulletin Solutions Inc.