CREATE TABLE `test` (
`id` INT(8) ,
`num` INT(8) ,
他多数 (省略)
)
このようなテーブルがある場合、以下の2種類のインデックスのつけ方にどのような違いがあるのでしょうか?
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ベストアンサー
- 回答日時:
母体データ件数はどのくらいで、その内、何件くらい検索するのでしょうか?
母体件数が相当に少ない場合や、殆ど全件検索する場合は、インデクスを使わない方が性能を出せる場合があります。
データの並びは、保証されなくていいのでしょうか?
インデクスは、「データの絞込み」だけでなく、「ソート抑止」にも大きな効果があります。大規模なシステムで性能を出そうとする場合、「ソートを発生させない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
- 回答日時:
#2回答者です。
#2回答の文中に誤解を与える可能性がある表現があったので、追記しておきます。
# こういった類の質問は、どういう環境なのかにより違いがかなりあるため、
# 「EXPLAINをやってみて」と回答する人も多いです。
=====追記(ここから)=====
実際にEXPLAINによるアクセス計画の確認は、性能を出す上で不可欠な作業であり、実際の環境で実行してみる以外に、「こうすれば最適」という答えはありません。
=====追記(ここまで)=====
No.1
- 回答日時:
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)の方が好ましい。』など(適当です)。
使い分けの基準がよくわかりませんので、みなさんはどのように使い分けているのか教えて頂きたいです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Ruby pandasでsqlite3にテーブル作成・追加・読み出しでindexの取り扱い方教えてください 5 2023/03/08 09:57
- C言語・C++・C# 質問です 下記のコードを分かりやすく解説お願いします 初心者です #include ‹stdio.h 3 2022/05/26 22:03
- MySQL my_itemsテーブルのIDにAUTO_INCREMENT を追加ができるかで 1 2023/01/03 09:09
- MySQL テーブル作成時のカラムについて 2 2022/08/27 21:48
- MySQL 次の時間帯の勝率の合計を求めるSQL文 1 2023/07/04 17:12
- MySQL MYSQL エラー 2 2022/10/18 11:37
- PHP php テーブルが作成できない 1 2022/11/17 23:41
- MySQL php テーブルを作れない 2 2022/11/17 18:22
- MySQL エラー 1068 (42000): 複数の主キーが定義されていますエラー 2 2022/11/17 04:36
- MySQL あと、MySQLの文字コードはutf8 気になりますね 1 2022/12/01 07:22
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
VIEWの元のテーブルのindexって...
-
insertを高速化させたい
-
SQL Left Join で重複を排除す...
-
select文のwhere句に配列を入れ...
-
一つ前のレコードの値と減算し...
-
SQLサーバから、項目の属性(型...
-
複数テーブルのGROUP BY の使い...
-
PL/SQLの変数について
-
期間の重複を調べるSQL文につい...
-
テーブル名を省略して「h.id」...
-
SELECT~LIKE~の結果が変
-
[MySQL] UNIQUE制約の値を更新...
-
1テーブル&複数レコードの更新...
-
マイクラPC版のコマンドで効率...
-
URL と行番号の指定
-
副問合せの書き方について
-
上位3位を求めるSQL文は?
-
SQLにて特定の文字を除いた検索...
-
mysqlのload data infileで連番...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
VIEWの元のテーブルのindexって...
-
SQLサーバから、項目の属性(型...
-
select文のwhere句に配列を入れ...
-
副問合せの書き方について
-
マイクラPC版のコマンドで効率...
-
Unionした最後にGROUP BYを追加...
-
selectした大量データをinsert...
-
SQLにて特定の文字を除いた検索...
-
[SQLServer] テーブル名からカ...
-
1テーブル&複数レコードの更新...
-
inner joinをすると数がおかし...
-
クエリ表示と、ADOで抽出したレ...
-
ある条件の最大値+1を初番する...
-
sqlで、600行あるテーブルを100...
-
複数テーブルのGROUP BY の使い...
-
insertを高速化させたい
-
PL/SQLの変数について
-
キー毎の、ある列のmaxのレコー...
おすすめ情報