Page 1 of 1

Where is this query from?

Posted: Fri Sep 03, 2010 11:28 am
by Ciao121
Hi need to understand which page/script is originating the following query:

Code: Select all

Query SELECT p.post_id FROM phpbb_posts p WHERE p.topic_id = 94097 AND p.post_approved = 1 ORDER BY p.post_time DESC LIMIT 21581, 15
THank for your help.

Re: Where is this query from?

Posted: Fri Sep 03, 2010 11:32 am
by Ciao121
Someone moved my question here.. so maybe the query is from a mod?

Re: Where is this query from?

Posted: Fri Sep 03, 2010 11:36 am
by Drummer
Probably it is.
Havent you done any sort of debugging attempt?
Why do you need to track this specific query?

Re: Where is this query from?

Posted: Fri Sep 03, 2010 11:39 am
by Ciao121
It's taking long long time to execute on my db. up to 300/400 seconds sometimes :(

Re: Where is this query from?

Posted: Fri Sep 03, 2010 12:08 pm
by Sajaki
normally there should be an index on topic_id, 300/400 execution time seems excessive. try running these sql statements.

Code: Select all

REPAIR TABLE phpbb_posts QUICK;
OPTIMIZE TABLE phpbb_posts ;

Re: Where is this query from?

Posted: Fri Sep 03, 2010 12:13 pm
by Drummer
Very odd.

You can edit the /includes/db/ files to make it log certain queries. Use $_SERVER['SCRIPT_FILENAME'] to log the "running" file that called the query.

Re: Where is this query from?

Posted: Fri Sep 03, 2010 12:30 pm
by Brf
That is the regular select from viewtopic:

Code: Select all

// Go ahead and pull all data for this topic
$sql = 'SELECT p.post_id 
You could try the repair thing suggested by Sajaki, but once a topic gets so long, 21000 posts, it is taking a while to load it all.

Re: Where is this query from?

Posted: Sat Sep 04, 2010 5:52 pm
by Ciao121
The long time needed seems to be due the "AND p.post_approved = 1" part of the query.

It seems to me this filter should only be present in the query if topic is in a forum requiring messages to be approved. I don't have any forum requiring it (maybe I made a little confusion with permissions).

So, to solve, I manually changed viewtopic.php to don't "append " "AND p.post_approved = 1" to queries and this solved problem. Because I'm sure I'll never use this feature.

Long running queries are now executed in no more than 1 second (and not 400 :D ).

The same applies to "AND t.topic_approved = 1" and viewforum.php.

Thanks everyone.

Re: Where is this query from?

Posted: Sat Sep 04, 2010 11:54 pm
by 3Di
To me then, looks like a bug. What kind of database are you running there?

Re: Where is this query from?

Posted: Sun Sep 05, 2010 12:05 am
by Ciao121
Mysql. Had problem using MyISAM. Tried to switch to InnoDB but problem was still here.