Problem/Motivation
Having a Condition class that can be overriden by a third party database is important. The Condition class generates a significant amount of SQL string parts. The currently by Drupal core supported databases may not need such a class, but that does not mean that a third party database driver will not need it. It is fantastic that Drupal core supports more then only MySQL but lets not limit it by only adding what is needed for the by core supported database drivers.
Proposed resolution
Making the class Drupal\Core\Database\Query\Condition overridable by a database driver.
Remaining tasks
User interface changes
None.
API changes
Yes. Third party database drivers need to add a Condition class to their driver.
Data model changes
None.
The old issue summary
pwolanin just pointed me to
#592522: Hooks node_type, taxonomy and user knocks out our database server →
.
Digest version: The Apache Solr module has some queries that involve UPDATES with large subselects. Now, we support subselects in UPDATE queries just fine... as subselects. Unfortunately, MySQL's handling of subselects is crap, because it reruns the query for each record (in a select), does stupid stuff with temp tables (disk thrashing), etc. That's fine when the subselect gets a dozen records. When it's selecting a few hundred thousand taxonomy terms (as Solr does), MySQL stops off to its room and slams the door.
MySQL has an alternate syntax that uses Joins(!!!) within the Update query that is a lot faster, and the Solr D6 module is using that now. The D7 version, of course, is not, because it's being a good little module and using the DB API properly. UpdateQuery will therefore dutifully run the ANSI-proper but MySQL-horrid version, and the server will fall over. Fail.
So our options are:
1) Tell modules running such queries to check the DB type (that is still possible) and if they're on MySQL use db_query() directly with an Update query that does the MySQL-proprietary version. If not on MySQL, just do the normal db_update(). This would be the least effort for us in core.
2) Override __toString() for UpdateQuery_mysql and try to detect if we're dealing with a subselect that we can optimize, then try to optimize it. This would be the most elegant solution and the best for contrib, but I don't know how feasible it is in practice.
3) Peter suggested adding tagging support to UpdateQuery so that modules could tag them as "needs subselect optimization" and then offer suggestions about how to optimize the query. This would be a sort of extended version of #2, but is a deeper structural change.
I am open to suggestions as to the best route forward.