On 03 Jan 2007, at 17:14, Moshe Weitzman wrote:
on the same topic, the main node_load() query takes on average 18ms on groups site which seems too high given that groups isn't all that big a site yet. this is a *very* frequent query. the SQL is:
SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = <nid>
mysql> EXPLAIN SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = 2; +----+-------------+-------+--------+---------------------+--------- +---------+--------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+--------- +---------+--------------+------+-------------+ | 1 | SIMPLE | n | ref | PRIMARY,vid,uid,nid | PRIMARY | 4 | const | 1 | Using where | | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | drupal.n.uid | 1 | Using where | | 1 | SIMPLE | r | eq_ref | PRIMARY | PRIMARY | 4 | drupal.n.vid | 1 | | +----+-------------+-------+--------+---------------------+--------- +---------+--------------+------+-------------+ I tested this query on my localhost running CVS HEAD, and it is using keys/indices. I've only a dozen nodes on my test site which can make a difference. It would be interesting to compare this EXPLAIN with one from groups.drupal.org. (I'm currently on the train so I can't do that right now.) -- Dries Buytaert :: http://www.buytaert.net/