Wordpress performance issues Using temporary; Using filesort specific query

Discussion in 'Content Management' started by fpc, Mar 14, 2010.

  1. #1
    I'm having some performance issues by the way a query is run, and I'm not sure how to change it.
    mysql> explain SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND wp_postmeta.meta_key = 'Thumbnail-150x75'  GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: wp_posts
             type: ref
    possible_keys: PRIMARY,type_status_date
              key: type_status_date
          key_len: 124
              ref: const,const
             rows: 21058
            Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: wp_postmeta
             type: ref
    possible_keys: post_id,meta_key,meta_post_id
              key: post_id
          key_len: 8
              ref: mm.wp_posts.ID
             rows: 1
            Extra: Using where
    2 rows in set (0.00 sec)
    Code (markup):
    Using temporary; Using filesort is the bad part

    I want to use this:

    mysql> explain SELECT wp_posts.* FROM wp_posts  JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND wp_postmeta.meta_key = 'Thumbnail-150x75' ORDER BY wp_posts.post_date DESC LIMIT 0, 5\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: wp_posts
             type: ref
    possible_keys: PRIMARY,type_status_date
              key: type_status_date
          key_len: 124
              ref: const,const
             rows: 21058
            Extra: Using where
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: wp_postmeta
             type: ref
    possible_keys: post_id,meta_key,meta_post_id
              key: post_id
          key_len: 8
              ref: mm.wp_posts.ID
             rows: 1
            Extra: Using where
    2 rows in set (0.00 sec)
    Code (markup):
    Any suggestions?
     
    fpc, Mar 14, 2010 IP