Luke Welling asked:

Specifically, do we use MySQL specific syntax that is more efficient (but breaks elsewhere) or do we attempt to write lowest common denominator SQL that will run more places, but not run as efficiently on our primary target?

Neither: we use the already-existing methods, and have those examine db-specific attributes to modify their behavior. The SQL itself stays pretty basic: I don't know that I've ever seen SQL (in core anyway), that varied enough between backends to require a differentiation. If we do encounter such a thing, it's probably best to pick the simplest variation (if possible), or the MySQL one (if not), and have attributes determine which variant is used (e.g. if ( $dbr->left_join_expensive() )

Matt Flaschen wrote:

However, part of the optimization is choosing indices, which as you noted is db-specific (part of tables.sql)

Not sure what you mean - index hints? Yeah, that could be a little tricky, but luckily the Postgres part, at any rate, doesn't have to worry about those (as our planner is smart enough to pick the best index itself ;). I can't think of a clean way to abstract that anyway, as just needing an index hint for MySQL does mean the same is needed on Oracle, and vice-versa. So you'd already have a very database specific argument for each query anyway, such that you would never have to worry if other dbs had the same index.