[wp-trac] [WordPress Trac] #9937: Ability not to sort the result in wp_get_object_terms()

[WordPress Trac]

#9937: Ability not to sort the result in wp_get_object_terms()
---------------------------------+------------------------------------------
 Reporter:  vladimir_kolesnikov  |       Owner:  Denis-de-Bernardy
     Type:  enhancement          |      Status:  new              
 Priority:  normal               |   Milestone:  Unassigned       
Component:  Optimization         |     Version:                   
 Severity:  normal               |    Keywords:  has-patch        
---------------------------------+------------------------------------------
 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.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/9937>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software