[development] Guidelines for writing efficient SQL code

[Jamie Holly]

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
>