[development] Slow query log

[Dries Buytaert]

On 03 Jan 2007, at 16:22, Gerhard Killesreiter wrote:
> The other one was the harmless looking query:
>
> SELECT * FROM files f INNER JOIN file_revisions r ON f.fid = r.fid  
> WHERE r.vid = 38515 ORDER BY f.fid;
>
> an EXPLAIN revealed that it did not use any index. The reason is  
> that we have defined its index as  PRIMARY KEY (fid, vid).
>
> Chris Johnson explained:
>
>  vid is a constant, and we are ordering the resulting fids.
>  But in a composite key, MySQL cannot use (at least in version 4.x)  
> any of the components of the composite key if the first component  
> is not used.

I just tested and can confirm that this is the case using MySQL 4.1.

> So what I did is to switch the keys around:
>
> alter table file_revisions drop primary key, add primary key (vid,  
> fid);

Oh, yeah, this needs fixing in Drupal 5.  I suggest we start by  
creating an issue for it.  Good job, Gerhard and thanks for the help  
Chris!

Is the slow query log database specific or does it monitor across all  
databases?  I believe it is the former but I can't verify that as I'm  
on the train.  We'll probably want to profile groups.drupal.org and  
scratch.drupal.org as well.  Chances are we find more bad queries.

We still have a little bit of time to fix bad queries in Drupal 5.   
(Dealing with SQL updates in branches -- after the final Drupal 5.0  
release -- is often a pain, so I'd prefer to fix before releasing  
Drupal 5.0 final.)  Either way, I'd recommend that we all spend some  
time looking at our slow query logs -- especially if you're already  
on Drupal 5.

Note that we added a handy MySQL status page to Drupal 5.  Go to  
"Home › Administer › Logs › Status report > MySQL version number" or  
jump directly to:

   http://example.com/?q=admin/logs/status/sql

Thanks,

--
Dries Buytaert  ::  http://www.buytaert.net/