Do LEFT JOINS outperform INNER JOINS? Yes Do INNER JOINS outperform LEFT JOINS? Yes It depends on the query. The MySQL query optimizer treats both differently. That's why its best to try different methods of joins and analyze each query to see where the best performance can be gained, and dependent upon the usage of said query. Also on indexes you say "Therefore the proposed solution to enhance Drupal is to hunt for multi-column indexes and replace them with single indexes.". MySQL makes very effecient use of multi column indexes. On countless queries in Drupal changing to only single column indexes would result in full table scans, temporary tables and filesorts in numerous places. When writing queries they should be targeted for performance on all supported RDBMS, yet the emphasis should be put on MySQL. The simple fact that a vast (vast) majority of Drupal sites out there run MySQL is reason enough for that, and until there is some major cosmic shift in what hosting companies offer that's going to remain the fact. Perhaps a future feature would be to have the installer optionally determine which RDBMS is being used and have targeted indexes for all in the schema - say $schema['table']['indexes']['mysql']=array(...). $schema['table']['indexes']['pgsql']=array(...). If no RDBMS is specified then the indexes go everywhere ($schema['table']['indexes'] - same as now). || Jamie Holly http://www.intoxination.net http://www.hollyit.net Jean-Michel Pouré wrote:
Le mardi 25 août 2009 à 16:27 -0700, Domenic Santangelo a écrit :
Jean-Michel, I think you misunderstand joins as they pertain to best practice and performance. Are we all basically in agreement that INNER JOIN is the faster of the two for most purposes?
Dear Friends,
I rewrote the guide: http://drupal.org/node/559302
It will be expanded further in the next days.
Feel free to comment.
Kind regards, Jean-Michel