WordPress.org

Make WordPress Core

Opened 6 years ago

Closed 6 years ago

#7761 closed defect (bug) (fixed)

Unnecessary SQL causing slowdown for large sites

Reported by: Otto42 Owned by:
Milestone: 2.7 Priority: normal
Severity: normal Version: 2.6.1
Component: Optimization Keywords:
Focuses: Cc:

Description

In wp-includes/query.php, there is this code:

if ( !empty($q['category__in']) || !empty($q['category__not_in']) || !empty($q['category__and']) ) {
	$groupby = "{$wpdb->posts}.ID";
}

The category_in uses an inner join, and so it might be possible for it to cause duplicate rows that the group by eliminates. Maybe. I don't quite see it happening myself, but I can see that it is theoretically possible.

However, both category_not_in and category_and function by retrieving the post IDs from those specific categories and then creating a "posts.ID NOT IN" or "IN" bit in the where section that eliminates or adds those specific posts. In this situation, duplicates are not possible, since there is no join, and so the group by is wholly unnecessary.

Furthermore, on extremely large sets of posts (100k+), this extra group by can cause mysql to require a temporary table to hold the results. The performance drag of a "group by" in this case is just as bad, or worse, than using a "distinct".

Suggest altering this to remove the category_not_in and category_and's, as they don't require the groupby in the first place.

Change History (4)

comment:1 Otto426 years ago

Additional: support forum reference http://wordpress.org/support/topic/195967

comment:2 follow-up: ryan6 years ago

Note that categorynot_in uses a subquery if the DB version supports it. See #7599.

comment:3 in reply to: ↑ 2 Otto426 years ago

Replying to ryan:

Note that category_not_in uses a subquery if the DB version supports it. See #7599.

Yes, I noticed that recent change, but the problem still exists. Even with the subquery, the GROUP BY clause is unnecessary, as it's using "posts.ID NOT IN (subquery)", which is not substantially any different than "posts.ID NOT IN (list of post numbers)" for this purpose.

comment:4 ryan6 years ago

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

(In [8968]) Group by not needed for not_in and and taxonomy queries. Props Otto42. fixes #7761

Note: See TracTickets for help on using tickets.