Trying to optimize slow MySQL query

Databases
1

Hi there,

I have this MySQL query, which pulls the 7 most recent posts from the forums:


SELECT p.*, u.username, u.session_active, u.profile_snippet, t.topic_title
FROM forums_posts p 
INNER JOIN users u
	ON p.user_id = u.id
INNER JOIN forums_topics t
	ON p.topic_id = t.topic_id
ORDER BY p.post_id DESC 
LIMIT 7

Takes about ~7.5 seconds to execute… which is awfully slow.

An EXPLAIN reveals:

+----+-------------+-------+--------+------------------+----------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type   | possible_keys    | key      | key_len | ref                      | rows  | Extra                           |
+----+-------------+-------+--------+------------------+----------+---------+--------------------------+-------+---------------------------------+
|  1 | SIMPLE      | t     | ALL    | PRIMARY          | NULL     | NULL    | NULL                     | 34328 | Using temporary; Using filesort | 
|  1 | SIMPLE      | p     | ref    | user_id,topic_id | topic_id | 8       | -------------.t.topic_id |    21 |                                 | 
|  1 | SIMPLE      | u     | eq_ref | PRIMARY          | PRIMARY  | 8       | -------------.p.user_id  |     1 |                                 | 
+----+-------------+-------+--------+------------------+----------+---------+--------------------------+-------+---------------------------------+

SHOW INDEXES FROM forums_posts:

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
forums_posts	0	PRIMARY	1	post_id	A	851538	NULL	NULL		BTREE	
forums_posts	1	user_id	1	user_id	A	1896	NULL	NULL		BTREE	
forums_posts	1	topic_id	1	topic_id	A	77412	NULL	NULL		BTREE

SHOW INDEXES FROM forums_topics:

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
forums_topics	0	PRIMARY	1	topic_id	A	34140	NULL	NULL		BTREE	
forums_topics	1	forum_id	1	forum_id	A	27	NULL	NULL		BTREE

Any ideas as to how to optimize this query so it’s not so slow? I think I’m missing something or have an improper index strategy. Thanks in advanced.

2

You should have a look at “Using temporary; Using filesort”. This is what is slowing your SQL down as it is writing to disk and sorting on disk. You might want to have a look at increasing the sort_buffer_size on your server to have the sorts performed in memory and not on disk.

3

I’ve increased the size of sort_buffer_size to nearly 10M and haven’t seen any improvement in performance of this query.

The reason why this query was so slow was because of the ORDER BY clause. It would appear that, for whatever reason, ORDER BY can’t use the indexes I have on the table to sort the result set (in this case, get the 7 most recent posts). What I’ve done to optimize this query is add a WHERE clause like so:

WHERE p.post_id > (SELECT MAX(post_id) - 7 FROM forums_posts)

This will return a physical result set of 7 rows at all times, which means that the ORDER BY clause will only ever have to sort 7 rows, causing the query to execute much faster.

Problem solved!

4

You’re selecting all fields in the forums_posts table do you need all of the fields? If not just select the required fields only as it should then cut down on the memory needed to process the query