[development] Slow query log
gerhard at killesreiter.de
Wed Jan 3 15:22:39 UTC 2007
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.
More information about the development