インデックスはどこに貼るべきか？

無知な私に教えてくださいm(_ _)m
Where文でよく使う項目にインデックスを貼っていたのですが
ググったりして探してみるとどうも違うようですが、みんな曖昧な言いまわしで
よくわかりません。
たとえば、PostgreSQLで以下のようなテーブルがあるとします。
---------------------------
SNO　　　　　　　　　　NUMERIC(8) [PRIMARY KEY]
NAME　　　　　　　　　VARCHAR(100)
SEX　　　　　　　　　　 NUMERIC(1) -- 0:男、1:女
---------------------------
SNOは通し番号でPRIMARY KEYなのでインデックスが貼られますが
クエリー発行時に「where SEX = 1 」とよく利用する場合にSEXにはインデックスを
貼るべきなのか貼らないべきなのかが分かりません。

試しに二つのテーブルを用意して実験してみました。
--テーブル1
create table test1(
sno serial not null,
name varchar(100),
sex numeric(2),
primary key(sno)
);

--テーブル２
create table test2(
sno serial not null,
name varchar(100),
sex numeric(2),
primary key(sno)
);

create index test2_indexA1
on test2
(sex);

この二つのテーブルにまったく同じデータを１０個ほど入れて
・explain select * from test1 where sex = 1 ;
・explain select * from test2 where sex = 1 ;
を実行してみました。

test1のほうは
Seq Scan on test1 (cost=0.00..22.50 rows=5 width=183)
Filter: (sex = 1::numeric)
という結果でした。

test2のほうは
Index Scan using test2_indexa1 on test2 (cost=0.00..17.07 rows=5 width=183)
Index Cond: (sex = 1::numeric)
という結果でした。

データ数も少ないのでコストはあまり変わりませんがusing indexと出てるのでtest2のほうが
インデックスが使われています。ということはやはりよく検索する項目にはインデックスを張るべきなのでしょうか？？

　　(　ﾟдﾟ)
＿(__つ/￣￣￣/＿
　　＼/　　　　/

　　　　_, ._
　　（　ﾟ Дﾟ）
＿(__つ/￣￣￣/＿
　　＼/　　　　/


　(　ﾟдﾟ)
＿(__つ/￣￣￣/＿
　　＼/　　　　/

　　　　_, ._
　　（；ﾟ Дﾟ）
＿(__つ/￣￣￣/＿
　　＼/　　　　/


　　(ﾟдﾟ　)
＿(__つ/￣￣￣/＿
　　＼/　　　　/


　(дﾟ　)
＿(__つ/￣￣￣/＿
　　＼/　　　　/

判らなきゃ聞かずに勉強しろ。
このあたりはSQL知ってるレベルから、DB使えるってレベルへの大事な部分だ。

Postgresってコストベースでしょ?
たかだかデータ10件でインデックススキャンを選択するオプティマイザ
って信用できるのだろうか。

それはともかく、

>クエリー発行時に「where SEX = 1 」とよく利用する場合にSEXにはインデックスを
貼るべきなのか貼らないべきなのかが分かりません。

SEXって男性、女性(+NULL?)しかないわけでしょ? そのフィールドに
インデックス貼って本当に効率がどこまで上がるのかな? それから、
>>4も書いているとおりそんな単純な問題ではないから、基礎から
勉強しましょう。
＃まあプランを検証しているだけマシか…

いや、単発質問スレ立ててる時点で勉強しても許しがたい

お尻に張ってください

尻でもSEXでもいいが、張って高速化できるんなら張れば。
ちなみにDBMSやindexの種類にもよるが、値の分布が少ない場合はあんまり効果無いぞ。
場合によっちゃ、張っても全スキャンの方が速いと判断して使ってくれない場合もある。

昔は、使ってくれないだけならまだしも、使った上に全件より遅くなったりしたもんだ。
今でもそういうDBMSあるだろね。

SEXの値のデータ分布が 1 対 1 なら、あまり意味が無い
男(0)：女(1)=9：1なんかで SEX = 1 という検索を行う必要があれば、インデックスを張る価値があるよ
この場合、SEX = 0 の検索では効果はないけどね

と経験上の話を書いたけど、残念ながら Oracle(たしか8iくらい？) は SEX = 1 のような検索でも
インデックスは使ってくれなかった。
入力された検索条件をユーザープログラム側で解析して、SEX = 1 となるような
検索になるときは、ヒントでインデックスを使うように指示した記憶がある。
ある特殊な検索だったんだけど、結果を出すのに数十秒かかってた処理が
一瞬で返るようになってビックリした。

結局、その検索条件でデータをガッツリ絞り込めることが保証されるなら
インデックスを張るべきだね。

まぁ、列のカーディナリティが低いなら、ビットマップインデックスはるか、
パーティションテーブル使った方がいいかもな

男と女のデータの数が1:1でも
インデックスを使うとデータは半分になるわけだが。

>>12
ほぅ・・・
100万件登録されているテーブルで50万件をインデックスにより特定して全スキャンさせるのか？
データ分布が1対1で、レコードをある程度絞り込めるならインデックスを使う必要はないだろ。

>>10の考え方は、インデックスをインデックスらしく使うのではなく
インデックスを介して、そのノードが保持するテーブル内に散乱したレコードの
行番号リストを得ることを目的としてるんだが。

カーディナリティが低い場合はインデックスを忘れろ。

> 100万件登録されているテーブルで50万件をインデックスにより特定して全スキャンさせるのか？
100万件全スキャンさせるのよりも、50万件全スキャンさせるほうが
半分ですむ。

>>15
インデックスたぐるコストのほうが、べた舐めよりかかる。

多くの場合、インデックスは昇順でならんでいる。
だから最初と最後の位置を特定すれば、あとは舐めるだけと変わらない。
データが少ない場合は、最初と最後の位置を特定するコストに相殺されるが、
データが多くなると、スキャンする範囲が狭くなるので有効。

それはindexのリーフに必要なデータが揃っている場合だけな。

select SEX from TABLE where SEX=1

だーね、SQL鯖でいうクラスタ化インデックスって場合。

せめて column 名は GENDER にしないか。

>>20
甘いな・・・
若手女プログラマに列名を言わせるのが楽しいんだろ
顔真っ赤にして言われると、こっちも苦笑いするしか無いがな

30過ぎの負け犬だと躊躇することなく列名を発するからツマラン

皆さん！レスありがとうございます。
B-TREEの基礎から勉強してきました。インデックスとは何かが良くわかりました。
奥が深くて回答しづらいのも良くわかりました。こんなスレたててすみませんでした。

オカマの思う壺
ttp://megabbs.com/pickles/index.html

>>22
んでどういう見解に至ったか説明せい。
カーディナリティという言葉を含めて。

なにげに良スレ化。

ほしゅ

インサートとかセックスとか、童貞にはハァハァ対象だな。

インデックスたん(;´Д｀)ﾊｧﾊｧ

>>25
できるよ。
以上。
はい次の方。

ソートで使う列（値はバラバラ）に
インデックスは効果ありますか？

>>30
意味はあるよ。
でもバラバラにインデックスを定義するよりも、複合インデックスにした方が効果的なのは言うまでもない。

>>10
Oracle8iのオプティマイザは糞だからな。
統計情報を採取した後に、急激にパフォーマンスが悪くなるなんてこともあった。
現在のバージョンのオプティマイザは優秀になったが。

Oracle10gや11gは、カーディナリティが低いSEXのインデックスでも使用してくれるだろう。
だが1つだけ注意しないといけないことがあってな。
それはSEXの抽出条件にバインド変数を使用してしまうと、
女で抽出する場合と男で抽出する場合でも、同じ実行計画を組み立ててしまうということ。

だから、ｵﾏｲの案のようなそういう特殊条件をやる場合は、
SEXの抽出条件にバインド変数を利用しないようにしないと問題が発生するだろう。

やべ、3年前のレスに返信してしもうたわｗ
ﾊｽﾞｶｼｰ　（/ω＼）

　ｘ‐‐―…―‐--ｘ
人人　　　　　　　　＼
　 　（ｰ:‐:‐:‐:‐:‐:‐:‐:i　ヽ
　マ （＿＿＿＿_____!　 !
　ジ （ﾉへﾊﾉﾊﾉィi::i::ﾊ　!
　で （ｒ心｀ 　 ｒ心ﾍ::l::!｜
　 っ（弋ｿ　　弋ｿ}ﾉ::N.｜
　!? （:::ｰ''／＼ｰ'::Y:::|　 て
::ﾊ⌒i u　＼／　 .ｲ:|:|　　(
｀ヽ::i:|＼｀　　７{ ﾉ:|::!:!　|

ビットマップはテーブルの目的によっちゃ使わないほうが良い事もある。

トランザクション系なら避けたほうがいい。

おいらは本番で痛い目を見た......

MySQL5.x系を使っているのですが、どうも件数が遅くなりそうで心配です。

簡単に言うと、名簿を作っているのですが、
'部'　'課'　'係'　というカラムで個人情報があって、それに対して、

( 部１ = 部２　and 課１ = null and 係１ ＝ null ) or
( 部１ = 部２　and 課１ = 課２ and 係１ ＝ null ) or
( 部１ = 部２　and 課１ = 課２ and 係１ ＝ 課２l ) or

というマッチングを頻繁にやってます。

この場合、部、課、係の複合インデックスのほかに、部、課、係それぞれの
インデックスを作ることは効果あるでしょうか？

エンジンによっても違うらしいから
explainしてみるか実測してみたら？

やっぱり個別インデックスをつけた方が速かったです。
エンジンは、ISAMを使っています。

更新時に重くなければ、このままインデックスをつけて
やってみます。混雑時じゃないと実測できないけど・・・。

　　　　　　　　　　　　　 _..　-――-　._
　　　　　　　　　　　 .／ ,―――‐-　.._` .、
　　　　　 　 x　　　/　 ./　 ／　／　 　 ｀`＼.　 ＋
　　　　　　　　 　 /_.. ィ7T.ﾌ厂￣｀ﾌｉ ‐-　._ |〉 　 　 x
　　　　 　 .x　 　 !　￣ﾌ/l/_×／/　|ﾊハl　.ﾄ、　 x
　　　　|! /　　 　|　　／|,ｲ._T_i`　 　.r≦lハ!|｀`　　　+
　　　　ll/_ 　 　 .|　　|　|'弋..!ﾉ 　 　 i'＋!ｌ　|
　　　/ ミr`! 　 /　　 l　|' ' '　 ,‐- ..__ﾞｰ' .!l .|
　　　ﾄ､ｿ　.!　./　　 .,!l .ﾄ､　　ｌ　　｀,! 　 .ハ.!　
　　　/ll＼ ｀テヽ、 /_,| |l: ＞ .ヽ.. ィ ＜l 　 l|
　　./' l|/l. >'　/ /＼. | | ＼ ＼ｰ'/ ./ ,,;:`:;'ﾞ"r;:ﾞc
　　'　 l|l l/　./ /　 　 | | 　_＼_×_/.ｨ'...二二二l ヽ
　　　　 | ヽ./ /　　　/|.|i彡_　　　　　　　　　　　＼＼
　　　　 | ／／　 .／ .l|| ´　 ￣,｢￣ ｢ ｌi￣二ﾆ -'´ ヽ.
　　　　└――'"l/／ .|! 　 / / ! .|　|' |l ／/
　　　　　　　　　/　＿_l＿/_/__.|__|__l_`_ｰ_'＿＿＿__.／

　　∧,,,∧　
　( 　・∀・)　ほー　それで
　 (　　：　)　
　　し─J

>>40

最近このコテよく見かける

お！今話題のスレタイ！

最初か最後のページだな。

痛いとこにでも貼っとけば良いんでない？

第1章　赤・緑・青編　　　　　　　　　　　　　1巻〜3巻
第2章　イエロー編　　　　　　　　　　 　　　4巻〜7巻
第3章　金・銀・クリスタル編　　　　　　　　8巻〜15巻
第4章　ルビー・サファイア編　　　　 　　　15巻〜22巻
第5章　ファイアレッド・リーフグリーン編　22巻〜26巻
第6章　エメラルド編　　　　　　　　　 　　　26巻〜29巻
第7章　ダイヤモンド・パール編　　　　　　30巻〜38巻
第8章　プラチナ編　　　　　　　　　　　　 　38巻〜40巻
第9章　ハートゴールド・ソウルシルバー編　41巻〜43巻
第10章　ブラック・ホワイト編　　　　　　　　43巻〜51巻
第11章　ブラック２・ホワイト２編 　　　　　　　52巻〜
第12章　エックス・ワイ編　　　　　　　　　　　 XY編2巻〜

腰

最初に無があった
無から有が生まれた
これが全ての真理

あそこ。

http://jbbs.shitaraba.net/sports/42269/