WordPress.org

Make WordPress Core

Opened 7 years ago

Closed 7 years ago

#5137 closed defect (bug) (fixed)

Taxonomy intersection queries are inefficient

Reported by: ryan Owned by:
Milestone: 2.3.1 Priority: normal
Severity: normal Version: 2.3
Component: General Keywords: taxonomy performance
Focuses: Cc:

Description

Performing intersection queries by equating like attributes is very inefficient when intersecting more than a few terms. Let's use the method where we perform a union query, count the number of terms for each post, and fetch the posts that have all terms.

Attachments (2)

intersection.diff (1.4 KB) - added by ryan 7 years ago.
New query for tag_slugand from Alexander Concha
intersection.2.diff (3.0 KB) - added by xknown 7 years ago.
New query for tag and category intersections

Download all attachments as: .zip

Change History (17)

ryan7 years ago

New query for tag_slugand from Alexander Concha

xknown7 years ago

New query for tag and category intersections

comment:1 xknown7 years ago

I improved a bit my first suggestion since category__and, tag__and and tag_slug__and should use similar queries, see intersection.2.diff.

comment:2 Otto427 years ago

Few problems:

  1. The resulting SQL these patches make doesn't work. Add spaces to the beginning of your quoted strings so you don't shove words together by accident like that.
  1. What in the heck is the point of the $where = "AND 0 = 1"; bits?

comment:3 ryan7 years ago

Without 0 = 1, if the intersection queries don't match anything, we fall through and do the regular front page query. We need to prevent that query from matching matching anything.

comment:4 ryan7 years ago

  • Resolution set to fixed
  • Status changed from new to closed

(In [6186]) New taxonomy intersection queries from xknown. fixes #5137

comment:5 ryan7 years ago

(In [6187]) New taxonomy intersection queries from xknown. fixes #5137 for 2.3

comment:6 Otto427 years ago

  • Resolution fixed deleted
  • Status changed from closed to reopened

As I pointed out, these won't work.

$tsql = "SELECT p.ID FROM ... blah ... ON (tt.term_id = t.term_id)"; 
$tsql .= "WHERE tt.taxonomy = '$taxonomy' AND t.$taxonomy_field IN ('" . implode("', '", $q[$item]) . "')"; 
$tsql .= "GROUP BY p.ID HAVING count(p.ID) = " . count($q[$item]);

You end up with no spaces between the ) and the WHERE or the ) and the GROUP. The query should fail unless MySQL is a lot smarter than any other database I've ever used.

comment:7 DD327 years ago

You end up with no spaces between the ) and the WHERE or the ) and the GROUP. The query should fail unless MySQL is a lot smarter than any other database I've ever used.

MySQL must be smarter than any other database you've used then. MySQL handles that SQL perfectly (It doesnt need the space between the bracket and WHERE/GROUP statement; If the brackets were left out of that statement however, MySQL wouldnt handle too well).

Mind you, I would suggest that the spaces be added at least for those who want to try and use other databases, as they're probably not as forgiving.

comment:8 ryan7 years ago

  • Resolution set to fixed
  • Status changed from reopened to closed

(In [6189]) Add some spacing. fixes #5137 for trunk

comment:9 ryan7 years ago

(In [6190]) Add some spacing. fixes #5137 for 2.3

comment:10 follow-up: Denis-de-Bernardy7 years ago

  • Resolution fixed deleted
  • Status changed from closed to reopened

why not use a subquery?

D.

comment:11 in reply to: ↑ 10 foolswisdom7 years ago

  • Resolution set to fixed
  • Status changed from reopened to closed

Replying to Denis-de-Bernardy:

why not use a subquery?

Why not attach a patch.

comment:12 Denis-de-Bernardy7 years ago

  • Resolution fixed deleted
  • Status changed from closed to reopened

because if you've the code in there already... just replace:

$post_ids = $wpdb->get_col($tsql);

(and the stuff that follows)

with:

$where .= " AND $wpdb->posts.ID IN ( $tsql ) ";

it save a return trip to the sql server and some needless array processing

D.

comment:13 follow-up: Denis-de-Bernardy7 years ago

Also, some testing will highlight that inner joins are a lot faster than left joins.

but if it's just to answer "attach a patch" like you did in your last post, just close the ticket. As much as I like WP, I've absolutely no time to waste writing patches that almost always get discarded.

comment:14 in reply to: ↑ 13 foolswisdom7 years ago

Replying to Denis-de-Bernardy:

but if it's just to answer "attach a patch" like you did in your last post, just close the ticket. As much as I like WP, I've absolutely no time to waste writing patches that almost always get discarded.

Denis, you know the routine. If you are finding your issues and patches aren't getting the attention they desire then bring it up on wp-hackers. Some of your tickets aren't good reports because they lack the necessary details, and don't allow myself or anyone else to advocate the problem's correction.

comment:15 ryan7 years ago

  • Resolution set to fixed
  • Status changed from reopened to closed

I think subqueries were introduced in 4.1 and we still have a 4.0 or greater dependency. When we bump the dependency we can move to subqueries.

I'll open another ticket about using INNER JOINs rather than LEFT JOINs. Closing this ticket.

Note: See TracTickets for help on using tickets.