WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 4 years ago

Last modified 3 years ago

#28922 closed enhancement (fixed)

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

Reported by: mbrandys Owned by: boonebgorges
Milestone: 4.4 Priority: normal
Severity: normal Version: 3.9.1
Component: Cache API Keywords: has-patch
Focuses: Cc:

Description

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.

Attachments (1)

28922.diff (581 bytes) - added by wonderboymusic 4 years ago.

Download all attachments as: .zip

Change History (5)

@wonderboymusic
4 years ago

#1 @wonderboymusic
4 years ago

  • Keywords has-patch added
  • Milestone changed from Awaiting Review to 4.4
  • Owner set to boonebgorges
  • Status changed from new to assigned

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

#2 @boonebgorges
4 years ago

  • Resolution set to fixed
  • Status changed from assigned to closed

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.

#3 @boonebgorges
3 years ago

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.

#4 @boonebgorges
3 years ago

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.

Note: See TracTickets for help on using tickets.