Can I make this MYSql query more efficient?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
intellivision
Forum Commoner
Posts: 83
Joined: Mon Aug 22, 2005 1:25 am
Location: Orbit

Can I make this MYSql query more efficient?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Can I make this MYSql query more efficient?

Post by josh »

post the results from explain
intellivision
Forum Commoner
Posts: 83
Joined: Mon Aug 22, 2005 1:25 am
Location: Orbit

Re: Can I make this MYSql query more efficient?

Post 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    
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Can I make this MYSql query more efficient?

Post by onion2k »

32452 rows with a filesort. No wonder it's slow. Add an index on topic_last_post_time.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Can I make this MYSql query more efficient?

Post 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.
intellivision
Forum Commoner
Posts: 83
Joined: Mon Aug 22, 2005 1:25 am
Location: Orbit

Re: Can I make this MYSql query more efficient?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Can I make this MYSql query more efficient?

Post 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.
intellivision
Forum Commoner
Posts: 83
Joined: Mon Aug 22, 2005 1:25 am
Location: Orbit

Re: Can I make this MYSql query more efficient?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Can I make this MYSql query more efficient?

Post 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.
intellivision
Forum Commoner
Posts: 83
Joined: Mon Aug 22, 2005 1:25 am
Location: Orbit

Re: Can I make this MYSql query more efficient?

Post 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 :?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Can I make this MYSql query more efficient?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Can I make this MYSql query more efficient?

Post 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.
intellivision
Forum Commoner
Posts: 83
Joined: Mon Aug 22, 2005 1:25 am
Location: Orbit

Re: Can I make this MYSql query more efficient?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Can I make this MYSql query more efficient?

Post by josh »

intellivision
Forum Commoner
Posts: 83
Joined: Mon Aug 22, 2005 1:25 am
Location: Orbit

Re: Can I make this MYSql query more efficient?

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