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