Make WordPress Core

Opened 13 years ago

Closed 11 years ago

#18081 closed enhancement (invalid)

Improve performance of taxonomy queries, avoid temporary tables

Reported by: tott's profile tott Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.2
Component: Taxonomy Keywords: westi-likes
Focuses: Cc:

Description

In various taxonomy queries "IN ( value )" is used even if there is only one value to check against. This is in some cases causing there creation of temporary db tables and bad performing queries.

The attached patch will ensure that only when there are more than one values to be added a "IN" statement is used. Otherwise a " = value" statement is performed.

Attachments (1)

transform_array_to_sql_condition.diff (9.6 KB) - added by tott 13 years ago.
Implementing transform_array_to_sql_condition() in taxonomy.php

Download all attachments as: .zip

Change History (8)

@tott
13 years ago

Implementing transform_array_to_sql_condition() in taxonomy.php

#1 @westi
13 years ago

  • Keywords 3.3-early westi-likes added
  • Milestone changed from Awaiting Review to Future Release
  • Version set to 3.2

This looks like an interesting optimisation.

Some thoughts:

  • Can you add before and after EXPLAIN outputs for the two queries - I like to see these on tickets as it makes it move obvious the reasoning behind the change :)
  • I think transform_array_to_sql_condition() might be nicer if it also took the field name and therefore returned either fieldname = 'xxx' or fieldname IN ( 'xxx', 'yyy' ) because something like WHERE term_taxonomy_id $term_ids_in is hard to read in a query and makes you double take.
  • I wonder if transform_array_to_sql_condition() should do any escaping on non integer values.

#2 @scribu
13 years ago

I refuse to believe that MySQL's query planner is so dumb.

Therefore, I would also like to see some EXPLAIN outputs. Also, please mention what version of MySQL you are testing against.

#3 @tott
13 years ago

Argh, turned out I was comparing apples with bananas. Had to different blogs open here. One on my local machine the other on my other machine. Sadly mysql is really not this stupid :) If there is still an interest to use this function I clean up this patch though. Queries are looking nicer and it makes it avoids some code duplication.

#4 @scribu
13 years ago

That's a relief. :)

A helper function for this might be a good idea, but give it a shorter name, i.e. wp_array_to_sql().

Also, I don't think the $array_map_sanitation arg is useful. The caller can just apply it themselves.

Just use absint() if they're integers or esc_sql() otherwhise.

Version 0, edited 13 years ago by scribu (next)

#5 @SergeyBiryukov
13 years ago

  • Keywords needs-patch added

#6 @westi
13 years ago

  • Keywords 3.3-early removed

#7 @wonderboymusic
11 years ago

  • Keywords needs-patch removed
  • Milestone Future Release deleted
  • Resolution set to invalid
  • Status changed from new to closed

The GROUP BY is what causes the temp table, the ORDER BY causes the Using filesort - the IN vs = makes seemingly no difference

Note: See TracTickets for help on using tickets.