[development] One-to-one tables considered harmful

[David Strauss]

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 at fourkitchens.com>
> To: <development at 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.
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 186 bytes
Desc: OpenPGP digital signature
Url : http://lists.drupal.org/pipermail/development/attachments/20070604/7186de34/attachment-0001.pgp