1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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