わたしは参加していないのですが YAPC::Asia Tokyo 2014 で次のような発表があったようです。
このセッションを聞いている人を WHERE gender = 'male' なら ORDER BY を狙った方がいいだろうし、WHERE gender = 'female' なら WHERE を狙った方がいい。
ちょっと試してみます。
次のようなテーブルがあったとします。age は年齢で gender は性別です。
drop table if exists yapcasia; create table yapcasia ( id int not null primary key, age int not null, gender char(6) not null ); create index idx_yapcasia__age on yapcasia (age); create index idx_yapcasia__gender on yapcasia (gender);
次のようにデータを入れます。
time seq 10000 |
mysql test -e "
truncate table yapcasia;
load data local infile '/dev/stdin' into table yapcasia (@id)
set id = @id,
age = floor(rand() * 40 + 20),
gender = if(@id % 500, 'male', 'female');
analyze table yapcasia;
"
男性が 9980 人に対して、女性が 20 人しかいません。
select count(*) from yapcasia where gender = 'male'; /* 9980 */ select count(*) from yapcasia where gender = 'female'; /* 20 */
性別ごとに年齢の若い順に 5 人列挙します。
select * from yapcasia where gender = 'male' order by age asc limit 5; +-----+-----+--------+ | id | age | gender | +-----+-----+--------+ | 18 | 20 | male | | 51 | 20 | male | | 75 | 20 | male | | 122 | 20 | male | | 181 | 20 | male | +-----+-----+--------+ select * from yapcasia where gender = 'female' order by age asc limit 5; +------+-----+--------+ | id | age | gender | +------+-----+--------+ | 4000 | 20 | female | | 4500 | 20 | female | | 1500 | 22 | female | | 7500 | 22 | female | | 3500 | 22 | female | +------+-----+--------+
実行計画を見てみると、
explain select * from yapcasia where gender = 'male' order by age asc limit 5 \G /* id: 1 select_type: SIMPLE table: yapcasia type: index possible_keys: idx_yapcasia__gender key: idx_yapcasia__age key_len: 4 ref: NULL rows: 10 Extra: Using where */ explain select * from yapcasia where gender = 'female' order by age asc limit 5 \G /* id: 1 select_type: SIMPLE table: yapcasia type: ref possible_keys: idx_yapcasia__gender key: idx_yapcasia__gender key_len: 18 ref: const rows: 20 Extra: Using index condition; Using where; Using filesort */
男性を検索するときは ORDER BY 狙いでインデックスが、女性を検索するときは WHERE 狙いでインデックスが使われています。
InnoDB の統計情報はカーディナリティが極端に低い列だと値ごとの統計情報も持っているのでしょうかね？
この例なら yapcasia (gender, age) なインデックスがあれば両狙いになるのは言うまでもありません。
drop index idx_yapcasia__age on yapcasia; drop index idx_yapcasia__gender on yapcasia; create index idx_yapcasia__gender_age on yapcasia (gender, age); analyze table yapcasia; explain select * from yapcasia where gender = 'male' order by age asc limit 5 \G /* id: 1 select_type: SIMPLE table: yapcasia type: ref possible_keys: idx_yapcasia__gender_age key: idx_yapcasia__gender_age key_len: 18 ref: const rows: 5078 Extra: Using where; Using index */ explain select * from yapcasia where gender = 'female' order by age asc limit 5 \G /* id: 1 select_type: SIMPLE table: yapcasia type: ref possible_keys: idx_yapcasia__gender_age key: idx_yapcasia__gender_age key_len: 18 ref: const rows: 20 Extra: Using where; Using index */
