インデックスの一意な値の数について

質問

インデックスを下記のように作成しているのですが、
MySQLはインデックスは同時に１つしか使用されないようなので
どれを消そうか迷っています。

約177万件レコードがあるのですが、レコード数のうち
一意な値の数が何割以下ならインデックスを
張る意味がないといった基準はあるでしょうか？

一意な値の数
1.1772253(PK int型)
2.2338(idx int型)
3.1122(idx int型)
4.18(idx int型)

回答No.1

chukenkenkou · ベストアンサー

>MySQLはインデックスは同時に１つしか使用されないよう

MySQLのバージョンは、何でしょうか？
当方、WindowsXP Home Editionで、MySQL 5.0.19-ntを使用していますが、「同時に１つのインデクスしか使われない」ということはありません。

実験的に、次の表定義＆インデクス定義しました。

create table t1
(c1 int primary key,
c2 char(1),
c3 varchar(5),
c4 timestamp,
c5 text);
create index t1ix2_4 on t1(c2,c3,c4);
create index t1ix4 on t1(c4);
create index t1ix5_4 on t1(c5(60),c4);

20000件のデータを格納し、以下のSQLのEXPLAINを確認しました。

explain
select * from t1
where c1 between 1001 and 5000
and c2 between 'b' and 'f'
and c4>='2007-11-15'
;

結果は、primary key、index t1ix2_4、t1ix4が使われています。

>約177万件レコードがあるのですが、レコード数のうち
>一意な値の数が何割以下ならインデックスを
>張る意味がないといった基準はあるでしょうか？

B-TREEインデクスの話としては、「性別」の話がよく出ますね。2種類しか値がないなら、B-TREEインデクスを付ける効果はないと。
ただし、どのシステムでも当てはまるような基準といったものは、存在しません。

>一意な値の数
>1.1772253(PK int型)
>2.2338(idx int型)
>3.1122(idx int型)
>4.18(idx int型)

それぞれ単一列で構成するインデクスを、4本定義しているのですか？
どういう条件指定をするのかや、order by、group by、distictを使ったりするのかが分からないと、適切なアドバイスはできません。

値の種類の話はひとまずおいてい置いて、単一列のインデクスが、本当に4本必要なのでしょうか？

select *
from t1
where c2 between a and b and c3>=d

のc2とc3のように、必ず組み合わせて検索条件を指定するような場合は、(c2,c3)の複数列で構成するインデクスの方が効率的に利用できる場合が多いです。
また、インデクスは、order byやgroup by、distinctでも使用される場合があります。

select *
from t1
where c2 between a and b
order by c2,c3,c4

のようなSQLの場合、(c2,c3,c4)で構成するインデクスがあれば、検索条件で利用されるだけでなく、ソート抑止もでき、性能の向上が可能です。

インデクスの有効利用等に関し、マニュアル説明箇所を示しておきます。
バージョンが不明なので、下記はMySQL 4.1版です。
http://dev.mysql.com/doc/refman/4.1/ja/query-speed.html

お礼

MySQLでは、１つのクエリを実行するとき、
１つのテーブルにつき１つのインデックスしか使用できないと
↓にあり、心配していました。http://labs.unoh.net/2007/06/mysql5.html　
うーん。この情報は何を指しているのかわからなくなりました。

explainやってみたら、
確かにintersect(xx_id,xx_id) Using where
というように複数のインデックスが使用されました。

インデックスの複合化については検討してみます。
ありがとうございます。

回答No.2

hrm_mmm

＞１つのテーブルにつき１つのインデックスしか使用できないと↓にあり、心配していました。
＞http://labs.unoh.net/2007/06/mysql5.html
＞うーん。この情報は何を指しているのかわからなくなりました。

ご呈示のサイト内をよく読めば、書いてありますが、MySQL4.0までの話です。
4.0までに比べ、4.1以降では、かなり機能拡張されています。index問題もその一つのようです。
ここの掲示板でもよく指摘されていますが、自分の利用しているMySQLのversionはしっかりと意識して使って下さい。

お礼

すごく頭がすっきりしました！
MySQL4.0の時に、分析ソフトを作っていたのですが
いくらインデックスを張っても検索が遅く挫折したのですが
バージョンによる機能不足が理由だったのですね・・
ありがとうございます。