Opened 13 years ago
Closed 11 years ago
#18081 closed enhancement (invalid)
Improve performance of taxonomy queries, avoid temporary tables
Reported by: | 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)
Change History (8)
#1
@
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 eitherfieldname = 'xxx'
orfieldname IN ( 'xxx', 'yyy' )
because something likeWHERE 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
@
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
@
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
@
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.
Implementing transform_array_to_sql_condition() in taxonomy.php