Page 1 of 1

Can I make this MYSql query more efficient?

Posted: Mon Sep 15, 2008 12:44 pm
by intellivision

Code: Select all

SELECT t . * , i.icons_url, i.icons_width, i.icons_height, tp.topic_posted, f.forum_name, f.forum_type, f.forum_flags
FROM phpbb3_topics t
LEFT JOIN phpbb3_topics_posted tp ON ( t.topic_id = tp.topic_id
AND tp.user_id =2 )
LEFT JOIN phpbb3_forums f ON f.forum_id = t.forum_id
LEFT JOIN phpbb3_icons i ON t.icon_id = i.icons_id
WHERE (
f.forum_recent_topics =1
AND t.topic_id NOT
IN ( 7, 11 )
AND t.forum_id
IN ( 7, 9, 10, 11 )
)
OR t.topic_type
IN ( 3 )
GROUP BY t.topic_last_post_id
ORDER BY t.topic_last_post_time DESC
LIMIT 4
It's taking 3-10 seconds to run on my virtual private server. It's a phpBB forum, and that query is grabbing the latest 4 topics from a fairly large db. I can certainly remove bells and whistles to get this working more quickly.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 8:11 am
by josh
post the results from explain

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 10:29 am
by intellivision
Is this what you mean?

Code: Select all

id       select_type     table       type    possible_keys       key     key_len     ref     rows    Extra
1   SIMPLE  t   ALL     PRIMARY,forum_id,forum_id_type,forum_appr_last,fid...   NULL    NULL    NULL    32452   Using where; Using temporary; Using filesort
1   SIMPLE  tp  eq_ref  PRIMARY     PRIMARY     6   const,asdb3.t.topic_id  1   Using where
1   SIMPLE  f   eq_ref  PRIMARY     PRIMARY     3   asdb3.t.forum_id    1   Using where
1   SIMPLE  i   eq_ref  PRIMARY     PRIMARY     3   asdb3.t.icon_id     1    

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 10:54 am
by onion2k
32452 rows with a filesort. No wonder it's slow. Add an index on topic_last_post_time.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 11:14 am
by josh
If you're using phpmyadmin theres tons of stats, like the # of temporary table being created, etc... You don't need to be a professional DBA, I find it's easy enough to look at.. phpmyadmin even highlights problem areas in red and then you can google whatever it is to learn more about the problem. You can usually catch performance issues before they even become issues.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 11:40 am
by intellivision
Index added. Performance is better, most executions yield 2.xxx seconds for that query. Occasionally I'll see ~6 seconds from that, unfortunately.

Thank you both.

I don't know if this is different than the other report, but here it is.

Code: Select all

id       select_type     table       type    possible_keys       key     key_len     ref     rows    Extra
1   SIMPLE  t   ALL     PRIMARY,forum_id,forum_id_type,forum_appr_last,fid...   NULL    NULL    NULL    32452   Using where; Using temporary; Using filesort
1   SIMPLE  tp  eq_ref  PRIMARY     PRIMARY     6   const,asdb3.t.topic_id  1   Using where
1   SIMPLE  f   eq_ref  PRIMARY     PRIMARY     3   asdb3.t.forum_id    1   Using where
1   SIMPLE  i   eq_ref  PRIMARY     PRIMARY     3   asdb3.t.icon_id     1    
jshpro2, the phpmyadmin features you mentioned... I don't see the highlighting function. phpMyAdmin - 2.8.2.4, MySql - 4.1.20

One more question: can I do something to cache the results from this query? It's probably run 10 times before the results would change. Alternatively, I'm going to knock pieces out of it to make it work faster. I just have too many rows in that db's tables for this to run well.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 11:44 am
by josh
mysql automatically caches queries, you'd have to do application side caching.

On the homescreen ( you may not have on a shared host ) you should have a screen that says how long the server has been running, queries per second, etc.. the stats will get more specific and detailed as you scroll down and problem #s will be in red font.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 12:04 pm
by intellivision
Found it. phpmyadmin home -> Show MySQL runtime information

I have 8 "red" rows, and interestingly, the Query Cache report shows all 0's, making it look like I have caching turned off. True?
Query cache
Variable ------- Value ------- Description

Qcache_free_blocks 0 The number of free memory blocks in query cache.
Qcache_free_memory 0 The amount of free memory for query cache.
Qcache_hits 0 The number of cache hits.
Qcache_inserts 0 The number of queries added to the cache.
Qcache_lowmem_prunes 0 The number of queries that have been removed from the cache to free up memory for caching new queries. This information can help you tune the query cache size. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.
Qcache_not_cached 0 The number of non-cached queries (not cachable, or not cached due to the query_cache_type setting).
Qcache_queries_in_cache 0 The number of queries registered in the cache.
Qcache_total_blocks 0 The total number of blocks in the query cache.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 12:09 pm
by josh
I believe so. You might wanna double check me, is your key cache size big enough? That can be another big bottleneck.. you might need to wait for more data since you just changed indexes, or reload the stats to start gathering fresh data so you can see how its changed.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 12:33 pm
by intellivision
In MySql home -> Server variables and settings:

key cache age threshold 300
key cache block size 1,024
key cache division limit 100

query cache size 0
have query cache YES
query cache limit 1,048,576
query cache type ON
table cache 64
thread cache size 0

I'm confused :?

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 12:43 pm
by Eran
Pay attention that in the sorted column Mysql does not use an index (it says null). You could try to provide hints (http://dev.mysql.com/doc/refman/5.1/en/index-hints.html), or maybe to break this query into two queries - each using an index appropriately. As others have said, the filesort instead of using index on the ordered by column is the biggest performance hog - you might even consider to specifically telling Mysql not to use the any other index just to force it to use the index on that column.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 12:58 pm
by josh
intellivision wrote:I'm confused :?
don't let an emotion like confusion stop you from learning something new.. there's always google. Just make backups of all your stuff before you start playing around.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 1:11 pm
by intellivision
jshpro2 wrote:
intellivision wrote:I'm confused :?
don't let an emotion like confusion stop you from learning something new.. there's always google. Just make backups of all your stuff before you start playing around.
I don't disagree that I need to read more about MySql. My confusion is regarding if caching is on or off. Thanks again for everyone's help in this thread. I've learned a lot today alone.

Re: Can I make this MYSql query more efficient?

Posted: Fri Sep 19, 2008 1:16 pm
by josh

Re: Can I make this MYSql query more efficient?

Posted: Fri Oct 03, 2008 11:45 am
by intellivision
Turned out to be very easy:

Code: Select all

WHERE t.topic_id > 32000
I just needed to limit the scope so the joins are small. Because this forum MOD only deals with the most recent n topics, in my case set to 4, it's easy to chop the size of the topic table (around 32200 rows).

Query time is now around .3 second.

I will need to go in occasionally and increase this topic_id to keep performance up.