[development] Slow query log

[Gerhard Killesreiter]

Dries Buytaert wrote:
> 
> 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.

drupaldb still has 4.0, we should change that to 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!

http://drupal.org/node/106624

> Is the slow query log database specific or does it monitor across all 
> databases?

All databases. I have now switched logging off, but I have 20MB of 
logfile to offer to trusted developers. I can extract site specific 
queries, if requested. I already made the groups.d.o log available to Moshe.

>  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 did already get one on groups. Should be easy to fix there.

> 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.

Such as groups.d.o. :)

Cheers,
	Gerhard