WordPress.org

Make WordPress Core

Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#9937 closed enhancement (fixed)

Ability not to sort the result in wp_get_object_terms()

Reported by: vladimir_kolesnikov Owned by: Denis-de-Bernardy
Milestone: 2.8 Priority: normal
Severity: normal Version:
Component: Optimization Keywords: has-patch
Focuses: Cc:

Description

In some situations (esp. when we delete a category which has a lot of posts in it) we don't care about the order of the terms.

Currently, if orderby is not passed to wp_get_object_terms(), it sorts the result by wp_terms.name. This is not optimal:

mysql> EXPLAIN SELECT t.term_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (2011) ORDER BY t.name ASC;
+----+-------------+-------+--------+-----------------------------------+----------+---------+-------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                     | key      | key_len | ref                                 | rows | Extra                                        |
+----+-------------+-------+--------+-----------------------------------+----------+---------+-------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | tt    | ref    | PRIMARY,term_id_taxonomy,taxonomy | taxonomy | 98      | const                               |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | tr    | eq_ref | PRIMARY,term_taxonomy_id          | PRIMARY  | 16      | const,wordpress.tt.term_taxonomy_id |    1 | Using index                                  |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                           | PRIMARY  | 8       | wordpress.tt.term_id                |    1 |                                              |
+----+-------------+-------+--------+-----------------------------------+----------+---------+-------------------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

If we add an ability not to sort the result, we get much better results:

mysql> EXPLAIN SELECT t.term_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (2011) ORDER BY NULL;
+----+-------------+-------+--------+-----------------------------------+----------+---------+-------------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                     | key      | key_len | ref                                 | rows | Extra       |
+----+-------------+-------+--------+-----------------------------------+----------+---------+-------------------------------------+------+-------------+
|  1 | SIMPLE      | tt    | ref    | PRIMARY,term_id_taxonomy,taxonomy | taxonomy | 98      | const                               |    1 | Using where |
|  1 | SIMPLE      | tr    | eq_ref | PRIMARY,term_taxonomy_id          | PRIMARY  | 16      | const,wordpress.tt.term_taxonomy_id |    1 | Using index |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                           | PRIMARY  | 8       | wordpress.tt.term_id                |    1 | Using index |
+----+-------------+-------+--------+-----------------------------------+----------+---------+-------------------------------------+------+-------------+
3 rows in set (0.01 sec)

As you can see, MySQL does not need to create a temporary table nor sort the result.

One of its practical applications is performance improvement in wp_delete_category(), those who are familiar with Taxonomy internals probably can find something else.

Attachments (1)

9937.diff (1.9 KB) - added by vladimir_kolesnikov 6 years ago.
The updated patch

Download all attachments as: .zip

Change History (9)

comment:1 @vladimir_kolesnikov6 years ago

  • Cc vladimir@… added

comment:2 @ryan6 years ago

Let's use 'none' to be consistent with other places, [11415]

comment:3 @vladimir_kolesnikov6 years ago

OK. Should I resubmit the patch?

comment:4 @Denis-de-Bernardy6 years ago

  • Milestone changed from Unassigned to 2.8

yes please

@vladimir_kolesnikov6 years ago

The updated patch

comment:6 @ryan6 years ago

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

(In [11457]) Add orderby = none option to wp_get_object_terms(). Props vladimir_kolesnikov. fixes #9937

comment:7 @ryan6 years ago

(In [11468]) Fix default orderby for tt_id query. see #9937

comment:8 @ryan6 years ago

(In [11469]) Fix default orderby for tt_id query. see #9937

Note: See TracTickets for help on using tickets.