複合インデックスで範囲検索が遅いとき
DDLとデータは末尾に示しますが、複合インデックスが効くはずのクエリなんだけどナめてる行数が多くて困ったというお話です。(MySQL 5.1.55)
このクエリで返ってくる行数は246件なんですが、それにしてはナめてる行数(rows)がやたら多いです。where句で使っているageとlast_updateとで複合インデックスを張っているのに、です。
between使用:
explain extended select id from betweeeeen where age between 30 and 35 and last_update >= 946612000 \G
↓
id: 1 select_type: SIMPLE table: betweeeeen type: range possible_keys: i1 key: i1 key_len: 10 ref: NULL rows: 4625 filtered: 100.00 Extra: Using where; Using index
範囲指定使用:
explain extended select id from betweeeeen where 30 <= age and age <= 35 and last_update >= 946612000 \G
↓
id: 1 select_type: SIMPLE table: betweeeeen type: range possible_keys: i1 key: i1 key_len: 10 ref: NULL rows: 4625 filtered: 100.00 Extra: Using where; Using index
で、
によれば、範囲検索の場合にこういう挙動になる、ダサいけどINで回避できるよ、って書いてあったので試したところ、
explain extended select id from betweeeeen where age in (30,31,32,33,34,35) and last_update >= 946612000 \G
↓
id: 1 select_type: SIMPLE table: betweeeeen type: range possible_keys: i1 key: i1 key_len: 10 ref: NULL rows: 246 filtered: 100.00 Extra: Using where; Using index
確かに rows が減りましたとさ、めでたしめでたし。
#!/bin/sh do_mysql() { mysql test } cat <<EOF | do_mysql drop table if exists betweeeeen; create table if not exists betweeeeen ( id int unsigned ,age int unsigned ,last_update int unsigned ,primary key (id) ,key i1 (age, last_update) ); EOF for i in $(seq 1 8000); do age=$((25 + $i % 15)) last_update=$((946600000 + ($i%13) * 1000)) echo "insert into betweeeeen values ($i, $age, $last_update);" done | do_mysql for i in $(seq 8001 9000); do age=$((40 + $i % 15)) last_update=$((946600000 + ($i%13) * 1000)) echo "insert into betweeeeen values ($i, $age, $last_update);" done | do_mysql last_update=946612000 cat <<EOF | do_mysql explain extended select id from betweeeeen where age between 30 and 35 and last_update >= $last_update \G EOF cat <<EOF | do_mysql explain extended select id from betweeeeen where 30 <= age and age <= 35 and last_update >= $last_update \G EOF cat <<EOF | do_mysql explain extended select id from betweeeeen where age in (30,31,32,33,34,35) and last_update >= $last_update \G EOF exit