[development] Slow query log

[Dries Buytaert]

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/