[wp-trac] [WordPress Trac] #28922: Post update caches queries causes "Using temporary" and "Using filesort"

[WordPress Trac]

#28922: Post update caches queries causes "Using temporary" and "Using filesort"
-------------------------+-----------------------------
 Reporter:  mbrandys     |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Cache API    |    Version:  3.9.1
 Severity:  normal       |   Keywords:
  Focuses:               |
-------------------------+-----------------------------
 While trying to optimize my WordPress application that I recently finished
 I started to run explain on some queries and found out that query in
 "update_object_term_cache" is causing "Using temporary; Using filesort"
 and query in "update_postmeta_cache" is causing "Using filesort". There
 are indexes for both term name and meta id but they are not used in
 current case.

 {{{
 SELECT t.*, tt.*, tr.object_id FROM OrKrXREB_terms AS t INNER JOIN
 OrKrXREB_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN
 OrKrXREB_term_relationships AS tr ON tr.term_taxonomy_id =
 tt.term_taxonomy_id WHERE tt.taxonomy IN ('navi_category', 'navi_place',
 'navi_tag') AND tr.object_id IN (105277, 104597, 107024, 106972, 111421,
 111417, 104549, 112582, 104548) ORDER BY t.name ASC;
 }}}


 {{{
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: tr
          type: range
 possible_keys: PRIMARY,term_taxonomy_id,object_id
           key: PRIMARY
       key_len: 8
           ref: NULL
          rows: 69
         Extra: Using where; Using index; Using temporary; Using filesort
 *************************** 2. row ***************************
            id: 1
   select_type: SIMPLE
         table: tt
          type: eq_ref
 possible_keys: PRIMARY,term_id_taxonomy,taxonomy,taxonomy_count
           key: PRIMARY
       key_len: 8
           ref: wp.tr.term_taxonomy_id
          rows: 1
         Extra: Using where
 *************************** 3. row ***************************
            id: 1
   select_type: SIMPLE
         table: t
          type: eq_ref
 possible_keys: PRIMARY
           key: PRIMARY
       key_len: 8
           ref: wp.tt.term_id
          rows: 1
         Extra:
 3 rows in set (0.00 sec)
 }}}

 {{{
 SELECT post_id, meta_key, meta_value FROM OrKrXREB_postmeta WHERE post_id
 IN (105277,104597,107024,106972,111421,111417,104549,112582,104548) ORDER
 BY meta_id ASC;
 }}}

 {{{
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: OrKrXREB_postmeta
          type: range
 possible_keys: post_id
           key: post_id
       key_len: 8
           ref: NULL
          rows: 102
         Extra: Using where; Using filesort
 1 row in set (0.00 sec)
 }}}

 As far as I know from looking on source of 'update_object_term_cache' and
 'update_postmeta_cache' functions there is no need to order terms by name
 and meta by id. If I'm wrong please correct me.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/28922>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform