Opened 15 years ago
Closed 15 years ago
#14699 closed enhancement (duplicate)
Optimize SQL resulting from custom taxonomy queries
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | Priority: | normal | |
Severity: | normal | Version: | 3.0.1 |
Component: | Query | Keywords: | has-patch needs-testing |
Focuses: | Cc: |
Description
The SQL generated by WP_Query::get_posts()
results in a get_objects_in_term()
and implode()
s the given post IDs into the posts query. The query can't be optimized at the database layer, and leads to slow performance with large numbers of posts. browse code
Attachments (1)
Change History (9)
@
15 years ago
aliases the term_taxonomy and term_relationship tables to avoid conflicting with other joins
#3
@
15 years ago
- Keywords has-patch needs-testing added; query mysql optimization removed
- Type changed from defect (bug) to enhancement
Some performance tests are in order.
#4
@
15 years ago
A note on performance: this could be made faster yet if the terms were resolved to term_taxonomy_id
s and only the the term_relationships
table was joined. The difficulty there is with get_term_children()
which expects and returns only term_id
s.
Adding this patch, however, is a good stepping stone to further optimizations.
#5
follow-up:
↓ 6
@
15 years ago
My first thought on this issue was to replace the PHP-created IN() list with a subquery. Often times two simple queries are faster than one complicated one.
#6
in reply to:
↑ 5
@
15 years ago
Replying to markjaquith:
The subquery is indeed faster. I ran some tests against a table set with about 15,000 terms, 181,000 term relationships, and 19,000 posts (running on a default MySQL config on a MacBook Pro). Here are the results:
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships tax_tr ON (wp_posts.ID = tax_tr.object_id) INNER JOIN wp_term_taxonomy tax_tt ON (tax_tr.term_taxonomy_id = tax_tt.term_taxonomy_id) WHERE 1=1 AND tax_tt.taxonomy = 'channel' AND tax_tt.term_id IN ('334540') 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;
This repeatedly returned results in about .4 seconds
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID IN ( SELECT SQL_NO_CACHE tax_tr.object_id FROM wp_term_relationships tax_tr INNER JOIN wp_term_taxonomy tax_tt ON (tax_tr.term_taxonomy_id = tax_tt.term_taxonomy_id) WHERE tax_tt.taxonomy = 'channel' AND tax_tt.term_id IN ('334540') ) 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;
This repeatedly returned results in about .08 seconds
The subquery approach is likely also better suited to doing intersections.
I think you need to use unique table references, or these will conflict with tag/tax queries, for one.