Ability not to sort the result in wp_get_object_terms()

[vladimir_kolesnikov]

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.

[ryan]

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

[vladimir_kolesnikov]

OK. Should I resubmit the patch?

[Denis-de-Bernardy]

yes please

[vladimir_kolesnikov]

The updated patch

[vladimir_kolesnikov]

Done

[ryan]

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

[ryan]

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

[ryan]

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