[development] Database / SQL future thoughts

[Nathaniel Catchpole]

There are plenty of places in Drupal where we can optimize the
database before leaving SQL behind. Search can already be factored out
to highly optimised systems like Apache Solr, but we still support SQL
based search in core (and it was made a lot faster for Drupal 6 than
in Drupal 5). So while I think pluggable field storage will be great,
it's not going to get us out of optimising for MySQL any time soon.
There's also a big difference between indexing content where the
primary record is still in the database, and swapping out the database
entirely.

On taxonomy specifically theres two issues with our current storage:
queries joining on the term_node table (as already pointed out by
Killes) - if you have conditions on one table and order by on another,
then MySQL can't use indexes efficiently. The term_node table might
disappear completely in Drupal 7 if vocabulary associations with nodes
move to the field API - http://drupal.org/node/412518 - field API
storage has it's own performance issues, but we need to deal with
those anyway. Or we might end up having both field tables and
term_node.

The other problem with taxonomy storage is term hierarchy.
http://drupal.org/node/344019 is being worked on (storing hierarchies
with nested sets keyed by rational numbers to save recursively walking
up and down trees) so we could do queries on the taxonomy tree
efficiently. There's also contrib modules for implementations of
nested sets and materialized path (for Drupal 5 / 6). Both issues
desperately need more eyes on them though, and sadly the discussion so
far suggests that the people writing of taxonomy's storage haven't
even bothered to look for them.

Nat