Where is this query from?

Discussion forum for MOD Writers regarding MOD Development.
Locked
Ciao121
Registered User
Posts: 239
Joined: Wed Jan 28, 2004 1:08 pm

Where is this query from?

Post 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.
Apri il tuo forum gratuito in 1 minuto.

Ciao121
Registered User
Posts: 239
Joined: Wed Jan 28, 2004 1:08 pm

Re: Where is this query from?

Post by Ciao121 »

Someone moved my question here.. so maybe the query is from a mod?
Apri il tuo forum gratuito in 1 minuto.

Drummer
Registered User
Posts: 199
Joined: Wed Jul 21, 2010 11:49 am

Re: Where is this query from?

Post by Drummer »

Probably it is.
Havent you done any sort of debugging attempt?
Why do you need to track this specific query?

Ciao121
Registered User
Posts: 239
Joined: Wed Jan 28, 2004 1:08 pm

Re: Where is this query from?

Post by Ciao121 »

It's taking long long time to execute on my db. up to 300/400 seconds sometimes :(
Apri il tuo forum gratuito in 1 minuto.

User avatar
Sajaki
Registered User
Posts: 1357
Joined: Mon Mar 02, 2009 1:41 pm
Name: Andreas
Contact:

Re: Where is this query from?

Post 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 ;

Drummer
Registered User
Posts: 199
Joined: Wed Jul 21, 2010 11:49 am

Re: Where is this query from?

Post 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.

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51937
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Where is this query from?

Post 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.

Ciao121
Registered User
Posts: 239
Joined: Wed Jan 28, 2004 1:08 pm

Re: Where is this query from?

Post 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.
Apri il tuo forum gratuito in 1 minuto.

User avatar
3Di
Former Team Member
Posts: 14837
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Where is this query from?

Post by 3Di »

To me then, looks like a bug. What kind of database are you running there?
Please PM me only to request paid works. Thx.
Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user
Extensions, Scripts, MOD porting, Update/Upgrades
:studio_microphone: Premium extensions @ The Studio

Ciao121
Registered User
Posts: 239
Joined: Wed Jan 28, 2004 1:08 pm

Re: Where is this query from?

Post by Ciao121 »

Mysql. Had problem using MyISAM. Tried to switch to InnoDB but problem was still here.
Apri il tuo forum gratuito in 1 minuto.

Locked

Return to “[3.0.x] MOD Writers Discussion”