You can efficiently apply criteria to several tables in a JOIN, but then you generally cannot use an index for sorting. Considering that an enormous number of Drupal queries follow a pattern of joining, sorting, and paginating, the efficiency of the sort is paramount to query performance. The database "eats" keys by satisfying WHERE criteria first. If removing the prefix used for the WHERE criteria leaves the ORDER BY criteria, then the key can also be used for sorting. http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html == Examples == Key: nid * Fast: WHERE nid = %d * Fast: ORDER BY nid Key: nid, vid * Fast: WHERE nid = %d * Fast: WHERE nid = %d AND vid = %d * Fast: WHERE nid = %d ORDER BY vid FGM wrote:
It seems to me you might be over-generalizing. Do you have any data to back up this assertion (URL ?) ? From what I remember from examining Firebird and Oracle query plans, they can apply criteria on several tables in a join without significantly impacting performance.
----- Original Message ----- From: "David Strauss" <david@fourkitchens.com> To: <development@drupal.org> Sent: Monday, June 04, 2007 8:19 AM Subject: Re: [development] One-to-one tables considered harmful
[...] There is no debate here, no matter what theoretical model you prefer. The database requires that all criteria for WHERE and ORDER BY exist in the same table to achieve acceptable performance. That table is {node}. The only way to realize the same performance goals without combining the fields in {node} is to create another table that contains the same fields, but just isn't called node. That would be pointless.