Improve performance of taxonomy queries, avoid temporary tables

[tott]

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.

Implementing transform_array_to_sql_condition() in taxonomy.php

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.

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.

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.

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 intval() if they're integers or esc_sql() otherwhise.