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 > 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.
Dear Jamie, 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 > 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. 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: Reference: According to http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html: MySQL cannot use an index if the columns do not form a leftmost prefix of the index. http://www.postgresql.org/docs/8.4/static/indexes-multicolumn.html 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 index. 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 system. I hope that you can comment my code and improve the English and technical aspects. Kind regards, Jean-Michel
signature.asc
Description: Ceci est une partie de message numériquement signée
