Gerhard Killesreiter wrote:
"If we for example have a user table, then this will contain mostly static data such as the username, password, address and other data. It does maybe also contain very dynamical data such as the time of the last login. From a theoretical point of view, these informations also belong into the user table but because of the physial implementation it will make a lot of sense to introduce an artificial 1:1 relation and to split off the pair (userid, lastlogin) into an extra table."
That works well if the information in the 1::1 table is loaded as supplementary information when you already know what rows you want in what order from one of the tables. It does not work well if your WHERE and/or ORDER BY conditions span the tables. It puts you in the temp table/filesort hell that is {node_comment_statistics}. Also, in the case of {node}, we almost always require that status = 1. By doing that, we start using {node} for query criteria. It severely restricts optimization options for queries that join {node} and a 1::1 table. Even if you use a table for supplemental data and never use it for filtering or ordering, you're doing an extra disk access to collect the supplemental information. I'm also not sure I buy the locking argument, as least for Drupal. If we use {node} for static data and {crazy} for dynamic data, it only helps us on MyISAM lock-contention for {node} reads. {crazy} stays flooded with locks, by design. But if {crazy} is usually joined with {node}, then queries have to wait on {crazy}'s locks, anyway. Right now, Drupal is doing such joins. Every time you load a node, it loads the dynamic data, like number of comments, with it. We would need to design Drupal to avoid reading dynamic tables until actually necessary.