No index for post_reported in phpbb_posts?

Discussion forum for MOD Writers regarding MOD Development.
Locked
Fisch.666
Registered User
Posts: 214
Joined: Sat Jul 30, 2005 10:58 am

No index for post_reported in phpbb_posts?

Post by Fisch.666 » Mon Nov 19, 2012 11:14 pm

Hi,

after digging through my slow query logfile of mysql i found this query which takes a about 0.9 seconds:

Code: Select all

SELECT post_reported, post_approved
                                                        FROM phpbb_posts WHERE forum_id IN (51, 1, 2, 52, 3, 50, 53, 48, 49, 54, 4, 5, 6, 7, 12, 46, 58, 65, 66, 67, 62, 63, 55, 45, 47, 56, 10, 9, 64, 68, 0) AND (post_reported = 1 or post_approved = 0);
This is the output of the EXPLAIN command for this query:

Code: Select all

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	phpbb_posts 	ALL 	forum_id,post_approved 	NULL	NULL	NULL	1590651 	Using where
After adding an index:

Code: Select all

ALTER TABLE `phpbb_posts` ADD INDEX `post_reported` ( `post_reported` ); 
to the phpbb_posts this is the new output of the EXPLAIN command for the statement above:

Code: Select all

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	phpbb_posts 	index_merge 	forum_id,post_approved,post_reported 	post_reported,post_approved 	1,1 	NULL	2 	Using union(post_reported,post_approved); Using wh...
and the query above now takes about 0.0012 seconds. (Note the 1590651 examined rows without an index vs 2 examined rows with an index. Yes, my board has more than 1.5 million posts)

Are there any reasons why there is now index for the post_reported field in the table?

Thanks in advance for a reply.

User avatar
John P
Registered User
Posts: 1237
Joined: Mon Jan 21, 2008 3:55 pm
Location: Netherlands
Name: John
Contact:

Re: No index for post_reported in phpbb_posts?

Post by John P » Mon Nov 19, 2012 11:23 pm

Sounds interessting
Image
Webhosting, Custom MODs, Technical management, MOD installation and Webdesign

User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: No index for post_reported in phpbb_posts?

Post by HGN » Tue Nov 20, 2012 12:16 am

Fisch.666 wrote:Hi,

after digging through my slow query logfile of mysql i found this query which takes a about 0.9 seconds:

Code: Select all

SELECT post_reported, post_approved
                                                        FROM phpbb_posts WHERE forum_id IN (51, 1, 2, 52, 3, 50, 53, 48, 49, 54, 4, 5, 6, 7, 12, 46, 58, 65, 66, 67, 62, 63, 55, 45, 47, 56, 10, 9, 64, 68, 0) AND (post_reported = 1 or post_approved = 0);
Could you tell where this query comes from (which page)?

Fisch.666
Registered User
Posts: 214
Joined: Sat Jul 30, 2005 10:58 am

Re: No index for post_reported in phpbb_posts?

Post by Fisch.666 » Tue Nov 20, 2012 12:29 am

Hi,

and thanks for your replies. Some minutes ago i had the same idea and just was searching for this query in my phpBB installation. The only file which contains this query are two files of this mod:

https://www.phpbb.com/customise/db/mod/ ... or_needed/

so this is probably not the problem of a standard phpBB installation.

User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: No index for post_reported in phpbb_posts?

Post by HGN » Tue Nov 20, 2012 12:41 am

I also thought the query came from a MOD, because I couldn't find it either in default phpBB.
Do you have the 1.0.4 version or the 1.0.5 version of that MOD?

Fisch.666
Registered User
Posts: 214
Joined: Sat Jul 30, 2005 10:58 am

Re: No index for post_reported in phpbb_posts?

Post by Fisch.666 » Tue Nov 20, 2012 12:48 am

Hi,

at the moment version 1.0.5 of moderator needed is installed at my phpBB 3.0.11. I just report this to the mod author and will link back to this thread.

User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: No index for post_reported in phpbb_posts?

Post by HGN » Tue Nov 20, 2012 2:00 am

Moving from "[3.0.x] Support" to "[3.0.x] MOD Writers Discussion" since it is MOD related.

Fisch.666
Registered User
Posts: 214
Joined: Sat Jul 30, 2005 10:58 am

Re: No index for post_reported in phpbb_posts?

Post by Fisch.666 » Thu Dec 06, 2012 5:20 pm

Hi,

another index

Code: Select all

ALTER TABLE `phpbb_privmsgs` ADD INDEX `message_reported` ( `message_reported` );
for this query:

Code: Select all

SELECT COUNT(msg_id) AS pms_count
                                FROM phpbb_privmsgs
                                WHERE message_reported = 1;
which speeds up a lot of this query:

Rows examined before the index: 138673
Rows examined after the index: 1

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: No index for post_reported in phpbb_posts?

Post by Oleg » Thu Dec 06, 2012 7:47 pm

Please report these either to the author of whichever modification added the queries (via customization database) or phpbb bug tracker for queries that are part of phpbb.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

Fisch.666
Registered User
Posts: 214
Joined: Sat Jul 30, 2005 10:58 am

Re: No index for post_reported in phpbb_posts?

Post by Fisch.666 » Thu Dec 06, 2012 8:20 pm

Hi,

it's already reported to the mod author at his support forum:

http://www.rmcgirr83.org/viewtopic.php?f=5&t=3085

Locked

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