Make WordPress Core

Opened 15 years ago

Closed 11 years ago

#11823 closed enhancement (fixed)

Improve SQL query used by get_terms()

Reported by: sirzooro's profile sirzooro Owned by: filosofo's profile filosofo
Milestone: 3.7 Priority: normal
Severity: normal Version: 3.0
Component: Taxonomy Keywords: has-patch
Focuses: Cc:

Description

#11076 / [12658] changes get_terms() so it uses SQL query with multiple conditions in WHERE clause - one for each included / excluded term. It will be better to change generated SQL from:

AND (t.term_id = 1 OR t.term_id = 2 OR ...)
AND (t.term_id <> 1 AND t.term_id <> 2 AND ...)

to:

AND t.term_id IN (1, 2, ...)
AND t.term_id NOT IN (1, 2, ...)

Attachments (6)

taxonomy.php.diff (1.6 KB) - added by sirzooro 14 years ago.
taxonomy.php.2.diff (1.9 KB) - added by sirzooro 14 years ago.
no loops this time
11823.diff (2.6 KB) - added by wonderboymusic 11 years ago.
11823.2.diff (2.8 KB) - added by duck_ 11 years ago.
11823.3.diff (3.9 KB) - added by wonderboymusic 11 years ago.
11823.4.diff (2.0 KB) - added by kovshenin 11 years ago.

Download all attachments as: .zip

Change History (17)

#1 @Denis-de-Bernardy
15 years ago

  • Milestone changed from 3.0 to Future Release

#2 @kevinB
14 years ago

  • Cc kevinB added

#3 @sirzooro
14 years ago

  • Keywords has-patch needs-testing added; needs-patch removed

OK, looks that I have to provide a patch for this :)

#4 @dd32
14 years ago

instead of those ugly loops, something similar to this might be possible?

$interms = array_map('intval', $interms);
$inclusions = ' AND t.term_id IN ( ' . implode(',', $interms) . ' )';

(note, Untested, and havnt looked at the code closer than the patch)

@sirzooro
14 years ago

no loops this time

#5 @wonderboymusic
11 years ago

  • Milestone changed from Future Release to 3.7

Refreshed, we should be using IN and NOT IN in most of these old functions that accept arrays of IDs

@duck_
11 years ago

#6 @duck_
11 years ago

11823.2.diff fixes a bug in the exclude_tree handling (the current patch will only exclude the last tree in the array).

#7 @wonderboymusic
11 years ago

  • Keywords commit added; needs-testing removed

.3.diff adds unit tests

#8 @wonderboymusic
11 years ago

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

In 25162:

Improve the include / exclude SQL generation in get_terms() by using IN and NOT IN where applicable. Adds unit tests for include / exclude.

Props sirzooro, duck_.

Fixes #11823.

#9 @wonderboymusic
11 years ago

In 25169:

No need to re-map the return values of wp_parse_id_list() to int.

See #22074, #11823.

@kovshenin
11 years ago

#10 @kovshenin
11 years ago

  • Keywords commit removed
  • Resolution fixed deleted
  • Status changed from closed to reopened

[25162] causes a database error if an include or exclude argument is not empty, but is also not really a term id. 11823.4.diff addresses that.

For reference, here's a sample SQL query it generates (note that 0):

SELECT t.*, tt.* FROM wptests_terms AS t INNER JOIN wptests_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag')0 ORDER BY t.name ASC
Last edited 11 years ago by kovshenin (previous) (diff)

#11 @wonderboymusic
11 years ago

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

In 25257:

Avoid database error when include or exclude is not really a term_id. Adds more unit tests.

Props kovshenin.
Fixes #11823.

Note: See TracTickets for help on using tickets.