複数のindexについて

質問

CREATE TABLE `test` (
`id` INT(8) ,
`num` INT(8) ,
他多数 (省略)
)

このようなテーブルがある場合、以下の２種類のインデックスのつけ方にどのような違いがあるのでしょうか？
phpmyadminで確認すると、(1)はインデックスが合体しており、(2)はインデックスが個別に分かれています。

(1)　ALTER TABLE `test` ADD INDEX ( `id` , `num` )

(2)　ALTER TABLE `test` ADD INDEX ( `id` )
　　ALTER TABLE `test` ADD INDEX ( `num` )

ちなみに、以下のようなSELECT文を用いる場合には、どちらのインデックスが適していますか？

SELECT * FROM test WHERE id='●' and num > '△'

( mysql5,MyISAM )

回答No.2

chukenkenkou · ベストアンサー

母体データ件数はどのくらいで、その内、何件くらい検索するのでしょうか？
母体件数が相当に少ない場合や、殆ど全件検索する場合は、インデクスを使わない方が性能を出せる場合があります。

データの並びは、保証されなくていいのでしょうか？
インデクスは、「データの絞込み」だけでなく、「ソート抑止」にも大きな効果があります。大規模なシステムで性能を出そうとする場合、「ソートを発生させないSQLを書くこと」をSQL作成基準に入れる場合もあります。

こういった類の質問は、どういう環境なのかにより違いがかなりあるため、「EXPLAINをやってみて」と回答する人も多いです。

id列、num列それぞれに、個別にインデクスを定義した場合、性能を出すことを期待できる操作例を示します。

(1)「=」条件や範囲条件
select * from test where id=値
select * from test where num between 値1 and 値2
(2)単一列でのソート
select * from test id between 値1 and 値2 order by id
select * from test num in(値1,値2,...,値n order by num
(3)or条件
select * from test where id=値1 or num=値2
(4)従属的な関係にない列のand条件
select * from test where id between 値1 and 値2 and num between 値3 and 値4

なお、(3)や(4)は、絞込み効果の低い条件の場合、複数のインデクスを使わない方（インデクスを一本だけ使用）が性能を出せる場合もあります。

id列、num列で一個のインデクスを定義した場合、性能を出すことを期待できる操作例を示します。

(1)「=」条件や範囲条件
→少なくとも先頭の構成列に、絞込みができる条件が必要
select * from test where id=値
select * from test where id=値1 and num between 値1 and 値2

(2)先頭列または先頭列から複数の列でソート
→列の指定順、昇降の指定がインデクスと合致している必要あり
select * from test where id between 値1 and 値2 order by id
select * from test where id between 値1 and 値2 order by id,num

お礼

返事が遅れてすみません。

単一では個別の方が性能がいいと予想していたのですが、or条件も個別の方がいいと思いませんでした。たいへん面白いデーターをありがとうございます。

母体件数を増やした上で、EXPLAINで色々実験してみたいと思います。

回答No.3

chukenkenkou

#2回答者です。
#2回答の文中に誤解を与える可能性がある表現があったので、追記しておきます。

# こういった類の質問は、どういう環境なのかにより違いがかなりあるため、
# 「EXPLAINをやってみて」と回答する人も多いです。

＝＝＝＝＝追記（ここから）＝＝＝＝＝
実際にEXPLAINによるアクセス計画の確認は、性能を出す上で不可欠な作業であり、実際の環境で実行してみる以外に、「こうすれば最適」という答えはありません。
＝＝＝＝＝追記（ここまで）＝＝＝＝＝

回答No.1

yambejp

idとnumは連携しているわけではないので(2)で十分では？
id='●'がconstでヒットしそうなのでnumのインデックスは
あまり活用されないかもしれません。

EXPLAINで挙動を確認しながらテストしていけばよいでしょう

補足

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

(1)　ALTER TABLE `test` ADD INDEX ( `id` , `num` )

(2)　ALTER TABLE `test` ADD INDEX ( `id` )
　　ALTER TABLE `test` ADD INDEX ( `num` )

の使い分けとしてはどのように考えればよろしいでしょうか。

例えば、『id と　num を同時に WHERE で使用する場合は (1) が好ましい。』『id のみ使用する場合があるならば、(2)の方が好ましい。』など（適当です）。

使い分けの基準がよくわかりませんので、みなさんはどのように使い分けているのか教えて頂きたいです。