Using temporary; Using filesort を回避したい

質問

はじめまして。よろしくお願いします。

下記の場合、Using temporary; Using filesort を回避するには
どうしたらよいでしょうか。

次の 2 つのテーブルがあります。

CREATE TABLE `test1` (
`f1` int(11) NOT NULL,
`f2` int(11) NOT NULL,
PRIMARY KEY (`f1`,`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test1` VALUES ('1', '2');
INSERT INTO `test1` VALUES ('1', '3');
INSERT INTO `test1` VALUES ('1', '4');
INSERT INTO `test1` VALUES ('2', '1');
INSERT INTO `test1` VALUES ('2', '3');

CREATE TABLE `test2` (
`f1` int(11) NOT NULL,
`f2` varchar(20) default NULL,
`f3` varchar(20) default NULL,
PRIMARY KEY (`f1`),
KEY `ix_f2_f3` (`f2`,`f3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test2` VALUES ('1', 'A', 'a');
INSERT INTO `test2` VALUES ('2', 'B', 'b');
INSERT INTO `test2` VALUES ('3', 'C', 'c');
INSERT INTO `test2` VALUES ('4', 'D', 'd');

次の SELECT を実行すると Using temporary; Using filesort が
現れてしまいます。

explain select test2.f2, test2.f3
from test1, test2
where test1.f1 = 1 and test1.f2 = test2.f1
order by test2.f2, test2.f3;

order by ... をなくすと問題ないのですが、ソートは必要です。

複数のキーに対してORDER BYを実行する場合
インデックスを使用できないとあるので
ix_f2_f3 は使用されず Using filesort となるのはわかるのですが、
Using temporary となる理由と回避方法がわかりません。

ご教示よろしくおねがいいたします。

回答No.1

chukenkenkou

アドバイスが無駄になる可能性があるので、バージョンを明記してください。MySQL4やMySQL5といった書き方でなく、少なくともMySQL 4.0、4.1、5.0といったことを明記願います。

お礼

回答ありがとうございます。

まだ開発環境ですが、5.0.15-nt になります。

実行環境は少なくともこれ以上のバージョンで
OS は Linux になる予定ですが、
こちらはまだ用意していません。

補足

この掲示板に不慣れなためどこに書いたらいいかわからないので
ここに書きます。自己レスです。

その後、開発中のテーブルにテストデータをある程度流し込んだところ、
インデックスが使用されるようになりました。

なので、投稿したテスト用テーブルにも
test1: 300 * 300 = 90,000 件の組み合わせ
test2: 300 件
を挿入して確認したところ、インデックスが使われるようになったようです。

+----+-------------+-------+--------+----------
| id | select_type | table | type | possible_keys
+----+-------------+-------+--------+----------
| 1 | SIMPLE | test2 | index | PRIMARY
| 1 | SIMPLE | test1 | eq_ref | PRIMARY
+----+-------------+-------+--------+----------

+----------+---------+-------------------------+------+-------
| key | key_len | ref | rows | Extra |
+----------+---------+-------------------------+------+-------
| ix_f2_f3 | 126 | NULL | 300 | Using index |
| PRIMARY | 8 | const,*******.test2.f1 | 1 | Using index |
+----------+---------+-------------------------+------+-------

投稿記事では数件の INSERT としていましたが、
開発中のテーブルでは数百件のデータを対象にしていたのですが、
オプティマイザにとっては少なすぎたということでしょうか？

MySQL の本格的な最適化は今回が初めてなのですごく苦労しています。