Slow query in homepage

Discussion forum for MOD Writers regarding MOD Development.
Locked
User avatar
AleSSaNDRo
Registered User
Posts: 132
Joined: Thu Mar 18, 2004 5:05 pm
Location: Milano
Contact:

Slow query in homepage

Post by AleSSaNDRo »

Hi, i want topic title in homepage of my forum and i don't want use mods of other authors because i don't want edit all styles.
I see that

Code: Select all

SELECT f.*, t.topic_title, t.topic_id FROM (phpbb_forums f) LEFT JOIN phpbb_topics t ON (f.forum_last_post_id = t.topic_last_post_id AND t.topic_moved_id = 0) ORDER BY f.left_id
remains in "Copying to tmp table" for many time... approximately for 50s.

What can I do? :roll:

Thanks
WebMaster of MondoWeb.net - Free hosting phpBB forum
I'm italian! :) Sorry for my bad english :oops:
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Slow query in homepage

Post by canonknipser »

create index on topic_last_post_id and maybe also on topic_moved_id
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
manic2
Registered User
Posts: 435
Joined: Thu Jun 12, 2008 9:16 pm

Re: Slow query in homepage

Post by manic2 »

I'm no expert however from the coding that I have done it looks like you are putting into memory every aspect of your forums table with every topic title & topic id.

Shouldn't you have a WHERE in there to select (at a guess) just the details from the last topic for each forum?

If you explain a little more with you are trying to do someone cleverer than me will probably be able to help.
manic
User avatar
AleSSaNDRo
Registered User
Posts: 132
Joined: Thu Mar 18, 2004 5:05 pm
Location: Milano
Contact:

Re: Slow query in homepage

Post by AleSSaNDRo »

canonknipser wrote:create index on topic_last_post_id and maybe also on topic_moved_id
Resolved. Thanks :)
WebMaster of MondoWeb.net - Free hosting phpBB forum
I'm italian! :) Sorry for my bad english :oops:
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Slow query in homepage

Post by canonknipser »

manic2 wrote:Shouldn't you have a WHERE in there to select (at a guess) just the details from the last topic for each forum?
a join is like a where-statement: http://en.wikipedia.org/wiki/Join_%28SQL%29#Inner_join
http://dev.mysql.com/doc/refman/5.5/en/join.html
So the statement
SELECT f.*, t.topic_title, t.topic_id FROM (phpbb_forums f) LEFT JOIN phpbb_topics t ON (f.forum_last_post_id = t.topic_last_post_id AND t.topic_moved_id = 0) ORDER BY f.left_id
can also be written as
SELECT f.*, t.topic_title, t.topic_id FROM (phpbb_forums f, phpbb_topics t WHERE f.forum_last_post_id = t.topic_last_post_id AND t.topic_moved_id = 0 ORDER BY f.left_id
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52627
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Slow query in homepage

Post by Brf »

The one with the "WHERE" is an Inner join though. It would not include forums that have no last post.
manic2
Registered User
Posts: 435
Joined: Thu Jun 12, 2008 9:16 pm

Re: Slow query in homepage

Post by manic2 »

Thanks for the explanation canonknipser & Brf, I've still a lot to learn!

desi90 you may also like to consider this:-
Erik Frèrejean wrote:phpBB also has it build in ;). proSilver and subSilver2 simply don't utilise this feature but the last topic title gets assigned to the template.

Code: Select all

'LAST_POST_SUBJECT'        => censor_text($last_post_subject),
depending of course exactly what you are trying to do.
manic
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Slow query in homepage

Post by canonknipser »

Brf wrote:The one with the "WHERE" is an Inner join though. It would not include forums that have no last post.
You are right - maybe my tired eyes read a "inner join" instead of a "left join"
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
User avatar
AleSSaNDRo
Registered User
Posts: 132
Joined: Thu Mar 18, 2004 5:05 pm
Location: Milano
Contact:

Re: Slow query in homepage

Post by AleSSaNDRo »

There are, also:

Code: Select all

# Time: 120830 10:42:31
# User@Host: - @ localhost []
# Query_time: 7.070655  Lock_time: 0.000107 Rows_sent: 250  Rows_examined: 22486
SET timestamp=1346316151;
SELECT SQL_CALC_FOUND_ROWS p.post_id
                        FROM phpbb_posts p
                        WHERE MATCH (p.post_subject, p.post_text) AGAINST ('+nyx  +cosmetic*  ' IN BOOLEAN MODE)
                                 AND p.forum_id NOT IN (23, 36, 39, 45, 46, 47, 48, 49, 50, 51, 52) AND p.post_approved = 1
                        ORDER BY p.post_time DESC
 LIMIT 250;
# Time: 120830 10:55:21
# User@Host: -] @ localhost []
# Query_time: 7.067124  Lock_time: 0.000059 Rows_sent: 1  Rows_examined: 382748
SET timestamp=1346316921;
SELECT MAX(p.post_id) as last_post_id
                        FROM phpbb_posts p , phpbb_topics t
                        WHERE p.forum_id = 44
                                AND t.topic_id = p.topic_id AND t.topic_approved = 1
                                AND p.post_approved = 1;
:(

phpBB 3's indices, I think, are bad for forums large.
WebMaster of MondoWeb.net - Free hosting phpBB forum
I'm italian! :) Sorry for my bad english :oops:
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Slow query in homepage

Post by canonknipser »

Where does those statements come from? Original phpBB or a modification?
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52627
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Slow query in homepage

Post by Brf »

Those certainly would not be from standard phpBB. There is no need to be searching for cosmetics, or for the last post in a forum when it is already in the forum's record as forum_last_post_id.
User avatar
AleSSaNDRo
Registered User
Posts: 132
Joined: Thu Mar 18, 2004 5:05 pm
Location: Milano
Contact:

Re: Slow query in homepage

Post by AleSSaNDRo »

My forum hasn't modifications :) It is original phpBB 3.0.11.

I see those statements in mysql in mysql-slow.log
WebMaster of MondoWeb.net - Free hosting phpBB forum
I'm italian! :) Sorry for my bad english :oops:
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52627
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Slow query in homepage

Post by Brf »

The first is probably someone searching.
The second query is only done when you delete the last post from a topic.

Neither should affect your homepage.
Locked

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