[wp-trac] [WordPress Trac] #37094: wp_get_nav_menu_items should use tax_query

[WordPress Trac]

#37094: wp_get_nav_menu_items should use tax_query
----------------------------------------+------------------------------
 Reporter:  spacedmonkey                |       Owner:
     Type:  enhancement                 |      Status:  new
 Priority:  normal                      |   Milestone:  Awaiting Review
Component:  Menus                       |     Version:  3.0
 Severity:  normal                      |  Resolution:
 Keywords:  has-patch needs-unit-tests  |     Focuses:  performance
----------------------------------------+------------------------------

Comment (by ocean90):

 '''Queries before:'''

 > SELECT tr.object_id FROM wp_term_relationships AS tr INNER JOIN
 wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE
 tt.taxonomy IN ('nav_menu') AND tt.term_id IN ('2') ORDER BY tr.object_id
 ASC


 {{{
 +----+-------------+-------+-------+-----------------------------------+------------------+---------+-------------+------+----------+--------------------------+
 | id | select_type | table | type  | possible_keys                     |
 key              | key_len | ref         | rows | filtered | Extra
 |
 +----+-------------+-------+-------+-----------------------------------+------------------+---------+-------------+------+----------+--------------------------+
 |  1 | SIMPLE      | tt    | const | PRIMARY,term_id_taxonomy,taxonomy |
 term_id_taxonomy | 138     | const,const |    1 |   100.00 | Using index
 |
 |  1 | SIMPLE      | tr    | ref   | term_taxonomy_id                  |
 term_taxonomy_id | 8       | const       |    4 |   100.00 | Using where;
 Using index |
 +----+-------------+-------+-------+-----------------------------------+------------------+---------+-------------+------+----------+--------------------------+
 }}}

 > SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID IN
 (6,23,160,290) AND wp_posts.post_type = 'nav_menu_item' AND
 ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.menu_order ASC


 {{{
 +----+-------------+---------------+------+--------------------------+------------------+---------+-------------+------+----------+-----------------------------+
 | id | select_type | table         | type | possible_keys            | key
 | key_len | ref         | rows | filtered | Extra                       |
 +----+-------------+---------------+------+--------------------------+------------------+---------+-------------+------+----------+-----------------------------+
 |  1 | SIMPLE      | wp_posts      | ref  | PRIMARY,type_status_date |
 type_status_date | 164     | const,const |    4 |   100.00 | Using where;
 Using filesort |
 +----+-------------+---------------+------+--------------------------+------------------+---------+-------------+------+----------+-----------------------------+
 }}}


 '''Queries with [attachment:37094.patch]:'''

 > SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy =
 'nav_menu' AND term_id IN (2)


 {{{
 +----+-------------+-----------------------+-------+---------------------------+------------------+---------+-------------+------+----------+-------------+
 | id | select_type | table                 | type  | possible_keys
 | key              | key_len | ref         | rows | filtered | Extra
 |
 +----+-------------+-----------------------+-------+---------------------------+------------------+---------+-------------+------+----------+-------------+
 |  1 | SIMPLE      | wp_term_taxonomy      | const |
 term_id_taxonomy,taxonomy | term_id_taxonomy | 138     | const,const |
 1 |   100.00 | Using index |
 +----+-------------+-----------------------+-------+---------------------------+------------------+---------+-------------+------+----------+-------------+
 }}}


 > SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships ON
 (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND (
 wp_term_relationships.term_taxonomy_id IN (2) ) AND wp_posts.post_type =
 'nav_menu_item' AND ((wp_posts.post_status = 'publish')) GROUP BY
 wp_posts.ID ORDER BY wp_posts.menu_order ASC


 {{{
 +----+-------------+----------------------------+--------+--------------------------+------------------+---------+-------------------------------------------------+------+----------+-----------------------------------------------------------+
 | id | select_type | table                      | type   | possible_keys
 | key              | key_len | ref
 | rows | filtered | Extra
 |
 +----+-------------+----------------------------+--------+--------------------------+------------------+---------+-------------------------------------------------+------+----------+-----------------------------------------------------------+
 |  1 | SIMPLE      | wp_term_relationships      | ref    |
 PRIMARY,term_taxonomy_id | term_taxonomy_id | 8       | const
 |    4 |   100.00 | Using where; Using index; Using temporary; Using
 filesort |
 |  1 | SIMPLE      | wp_posts                   | eq_ref |
 PRIMARY,type_status_date | PRIMARY          | 8       |
 wp_develop.wp_term_relationships.object_id      |    1 |   100.00 | Using
 where                                               |
 +----+-------------+----------------------------+--------+--------------------------+------------------+---------+-------------------------------------------------+------+----------+-----------------------------------------------------------+
 }}}

 I'm not really a MySQL expert but `Using temporary; Using filesort` sounds
 bad.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/37094#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform