[development] Guidelines for writing efficient SQL code
hovercrafter at earthlink.net
Wed Aug 26 12:30:20 UTC 2009
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
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). ||
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:
> It will be expanded further in the next days.
> Feel free to comment.
> Kind regards,
More information about the development