[development] Slow query log
dries.buytaert at gmail.com
Wed Jan 3 17:08:48 UTC 2007
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,
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
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:
Dries Buytaert :: http://www.buytaert.net/
More information about the development