[development] Guidelines for writing efficient SQL code
jm at poure.com
Wed Aug 26 13:40:26 UTC 2009
Le mercredi 26 août 2009 à 08:30 -0400, Jamie Holly a écrit :
> 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
> reason enough for that, and until there is some major cosmic shift in
> what hosting companies offer that's going to remain the fact.
I have been rewriting a generic guide for both MySQL and PostrgeSQL:
Guidelines for writing efficient SQL code http://drupal.org/node/559302
These are very generic guidelines explaining why you should always log
server-side queries, use Devel module and ANALYSE your queries.
I also explain the notion of sequential scan and CPU time needed.
Then I analyse simple facts using EXPLAIN.
> Also on indexes you say "Therefore the proposed solution to enhance
> Drupal is to hunt for multi-column indexes and replace them with
> indexes.". MySQL makes very effecient use of multi column indexes. On
> countless queries in Drupal changing to only single column indexes
> result in full table scans, temporary tables and filesorts in
About dual-field indexing: PostgreSQL and MySQL work the same way.
Some databases claim that a dual index works equally right and left.
This is not portable and probably not true, as multi-column indexing may
result in a sequential scan in complex queries:
MySQL cannot use an index if the columns do not form a leftmost
prefix of the index.
index could in principle be used for queries that have
constraints on b and/or c with no constraint on a — but the
entire index would have to be scanned, so in most cases the
planner would prefer a sequential table scan over using the
Therefore, when needed, it could be needed to add single indexes AND
keep dual indexes.
> Do LEFT JOINS outperform INNER JOINS? Yes
> Do INNER JOINS outperform LEFT JOINS? Yes
I don't believe so because of the nature of INNER JOINs which remove
NULL rows and thus consume more CPU time. They only work faster on
indexes on dual-fields when used properly.
This needs to be studied using ANALYSE on very large SQL servers, not
simple installs. Drupal website database probably is a good testing
I hope that you can comment my code and improve the English and
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Size: 197 bytes
Desc: Ceci est une partie de message num?riquement sign?e
More information about the development