Post update caches queries causes "Using temporary" and "Using filesort"

[mbrandys]

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.

[wonderboymusic]

In update_object_term_cache(), there is no need to order by name. Without it, query plan goes from Using temporary; Using filesort; Using index to Using index; Using join buffer

28922.diff​

[boonebgorges]

In 34217:

Omit the ORDER BY clause when updating object term cache.

The ORDER BY clause was forcing filesorts on large tables, and is
unnecessary, since term order doesn't matter when updating the cache.

Props mbrandys, wonderboymusic.
Fixes #28922.

[boonebgorges]

In 36056:

Order terms by 'name' when populating object term cache.

[34217] removed the ORDER BY clause from update_object_term_cache(), for
improved performance. But this proved to cause problems in cases where users
were expecting the results of get_the_terms() to be ordered by 'name'. Let's
revert the change for the time being, and look into more disciplined ordering
in a future release.

Props afercia.
See #28922. Fixes #35180.

[boonebgorges]

In 36057:

Order terms by 'name' when populating object term cache.

[34217] removed the ORDER BY clause from update_object_term_cache(), for
improved performance. But this proved to cause problems in cases where users
were expecting the results of get_the_terms() to be ordered by 'name'. Let's
revert the change for the time being, and look into more disciplined ordering
in a future release.

Merges [36056] to the 4.4 branch.

Props afercia.
See #28922. Fixes #35180.