SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to optimize slow MySQL query

    Hi there,

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

    Code mysql:
    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:

    Code:
    +----+-------------+-------+--------+------------------+----------+---------+--------------------------+-------+---------------------------------+
    | 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:
    Code:
    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:
    Code:
    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. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,028
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •