Drupal 7 performance wish list

By peterx on 6 Jul 2007 at 18:20 UTC

Drupal 7 is mentioned in http://drupal.org/gophp5. Time to start a Drupal wish list for 7.

For performance, I would start with a change to the node module to reduce the database overhead from revisions. Why does noad_load always join to revisions? We could put the current data in the node and make the revision table a record of other revisions. noad_load would have one less join.

All display processing would be from the node table and should work faster for logged in users who receive fresh data instead of cached. The revisions table would be used only in admin to create new revisions during workflow and to review revisions.

petermoulding.com/web_architect

Comments

Body content

Crell commented 6 July 2007 at 19:05

The body field is always stored in node_revisions now, so you have to join in order to get that field. However, one of the goals of the Schema work in Drupal 6 was to allow the load routines to be introspective and dynamically build larger queries that can pull the whole node in one or two queries instead of the 5-10 that it takes now. That makes the query more complex, of course, and so pre-compiling it once with a formal prepared statement would give a larger speed benefit than pre-compiling our current typical tiny query. PDO provides formal prepared statements on every database. :-)

Hopefully these pieces will come together somewhere in the Drupal 7 timeframe, but first we have to get Drupal 6 out the door.

--
Larry Garfield
http://www.garfieldtech.com/blog
http://www.palantir.net

--
Larry Garfield
http://www.garfieldtech.com/
Thinking Functionally in PHP: https://leanpub.com/thinking-functionally-in-php

Beware prepare

peterx commented 7 July 2007 at 05:18

I tried PDO prepare with PHP 5.1 and then then latest MySQL. Some SQL was slower with prepare-exec than query. Depends on MySQL version, the action, the fields that are supplied at execution time, the indexes, and the recent use of optimise, which could be in a monthly cron job.

petermoulding.com/web_architect

node revisions

peterx commented 7 July 2007 at 05:29

In my modules I select the latest revision using SQL similar to the following. It works and is fast in MySQL 5. The problem is implementing workflow where you want a revision other than the latest. The latest may not be marked for public display. When you add extra selection criteria to the SQL, everything becomes slowwwwww. Instead of caching the node-revision combination, it is easier to copy the current public revision into the node and have revisions visible only in the administration area. The final step in workflow would be to copy the latest public revision into the node.

select n.nid, r.title from {node} n
join {node_revisions} r on r.nid = n.nid
left join {node_revisions} r2 on r2.nid = n.nid and r2.vid > r.vid
where n.type = 'publisher' and r2.nid is null

petermoulding.com/web_architect

Module to revision

peterx commented 7 July 2007 at 06:08

Some of my modules attach similar attributes to nodes in revisions using secondary tables which is the Halal way of writing modules for Drupal. Revisions already contains optional fields including teaser. Why not add more optional fields for better performance?

I added node_revisions.web_site because several modules use a field named web_site and some modules add an extra table just for web_site. Adding web_site to node_revisions was a minor change that removes two whole tables which are looked up every time for the most popular node types on some of my sites. One site is now noticeably faster for logged in users.

The change to node.module showed that note_type includes the fields has_title and has_body which are used but not displayed anywhere on the administration pages. If I added has_web_site and web_site_title, I could switch the web_site field on for those modules that require it.

The overall change would be small and the performance benefit worth it. A generic approach would replace web_site with optional_text_1 and use optional_text_1_title to supply the field title.

Unused optional fields do not add to the overhead as much as parallel tables including CCK tables. The database is not in fifth, fourth, or third normal form which means an extra field will not break the design level. Would Drupal.org benefit from moving some popular module fields up to node or node_revisions?

petermoulding.com/web_architect