[wp-trac] [WordPress Trac] #24837: querying optimization for category AND searching

[WordPress Trac]

#24837: querying optimization for category AND searching
-------------------------------------+------------------------------
 Reporter:  robertv123               |       Owner:
     Type:  enhancement              |      Status:  new
 Priority:  normal                   |   Milestone:  Awaiting Review
Component:  Taxonomy                 |     Version:  3.6
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |     Focuses:
-------------------------------------+------------------------------

Comment (by archon810):

 I just ran into a very slow query (13-25s) just dealing with the simple
 category page. Upon further analysis, I found that mysql isn't using the
 `type_status_date` index and PRIMARY (when it does, the query runs a lot
 faster and says "using index" in both the query and subquery), but rather
 PRIMARY and `term_taxonomy_id`, which is a LOT slower.

 At the very least, FORCE or USE INDEX should be added here to force
 optimization and make it run faster.

 I'm playing with adding another index right now.

 The query in question btw (FORCE INDEX should be added after `FROM
 wp_posts`:

 {{{
 SELECT SQL_CALC_FOUND_ROWS
         wp_posts.ID
 FROM
         wp_posts
 INNER JOIN wp_term_relationships ON (
         wp_posts.ID = wp_term_relationships.object_id
 )
 WHERE
         (
                 wp_term_relationships.term_taxonomy_id IN (3, 5201, 9699,
 11025, 20317)
         )
 AND wp_posts.post_type = 'post'
 AND (
         wp_posts.post_status = 'publish'
         OR wp_posts.post_status = 'private'
 )
 GROUP BY
         wp_posts.ID
 ORDER BY
         wp_posts.post_date DESC
 LIMIT 0,
  10;
 }}}

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