[development] Slow query log

[Gerhard Killesreiter]

Hi there!

Today i had a look at drupal.org's slow query log after I found out it 
is collecting data. Note that the slow query log now also contains 
queries that don't use an index, no matter how fast.

There were 2 queries responsible for about 2/3 of the entries.

One is the query from drupal_is_denied, which is run on each bootstrap. 
This query has been improved for Drupal 5, but I think it coul dbe 
further approved by doing away with the two LOWER()s in there in case 
you just check an IP.

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.

So what I did is to switch the keys around:

alter table file_revisions drop primary key, add primary key (vid, fid);

Somebody might want to roll a patch or Drupal5.

Cheers,
	Gerhard