質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

Q&A

解決済

2回答

2935閲覧

sqlserverでのフルテキストインデックス+条件を付けると遅くなる

ycy

総合スコア19

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

0グッド

0クリップ

投稿2019/11/28 01:08

sqlserverでのフルテキストインデックス+条件を付けると遅くなる

AUTO_ID(キー列) │ TITLE │ PREF  │ STORE │
1 │あああ │ 大阪府  │ お店 │
2 │いいい │ 東京都  │ 個人 │
・・・下へおよそ1億レコード・・・

のようなテーブルがあり、TITLE列でフルテキストINDEXを行っています。
検索条件を降順限定としているため、当サイトでご教授いただいた内容で、

キーワード検索のみの場合は下記内容で問題なく実行できております。
select * from [TABLE_NAME]
where AUTO_ID in (
select AUTO_ID from [TABLE_NAME] where CONTAINS(TITLE,'"[KEYWORD]"')
)
ORDER BY AUTO_ID DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

TITLE部分のキーワード検索は問題ない速度で検索できるのですが、
都道府県指定などを指定すると劇的に重くなっており困っております。

SELECT * FROM [TABLE_NAME] WHERE AUTO_ID IN (
SELECT AUTO_ID FROM [TABLE_NAME] where CONTAINS(TITLE,'"中古"')
) AND PREF='大阪府' AND STORE='お店'
ORDER BY AUTO_ID DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

SELECT * FROM [TABLE_NAME] WHERE AUTO_ID IN (
SELECT AUTO_ID FROM [TABLE_NAME] where CONTAINS(TITLE,'"中古"')
AND PREF='大阪府' AND STORE='お店')
ORDER BY AUTO_ID DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

試しに用意してみたインデックスは

(1)非クラスター化インデックス
・AUTO_ID 降順
・PREF 降順
・STORE 降順

(2)非クラスター化インデックス
・PREF 昇順
・STORE 昇順
・AUTO_ID 降順

両者とも遅かったのですが、INDEXの張り方がまずいのか、SQLに問題があるのかわからない状況です。
TITLEでヒットする件数が少ない場合は、比較的高速に返ってきますが、多い場合は10秒以上かかっております。

イメージ説明

イメージ説明

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

前の回答でも申しましたように、selectで使用できるインデックスは一つです。
適切なインデックスにして下さい。

SQL

1SELECT * FROM [TABLE_NAME] WHERE AUTO_ID IN ( 2 SELECT AUTO_ID FROM [TABLE_NAME] where CONTAINS(TITLE,'"*中古*"' 3) 4 AND PREF='大阪府' AND STORE='お店') 5ORDER BY AUTO_ID DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

上記であれば、以下のようなインデックスが必要です
(PREF, STOR, AUTO_ID DESC)
または
(AUTO_ID DESC, PREF, STOR)

追記

SQLからはプライマリーを使うように働くはずですけど、昇順と降順が混在すると意図通りには働かない顔も知れませんね。

AUTO_IDは名前から推測するに、オートナンバーですよね。
datetime型などでデフォルトGETDATE()の作成日時項目を追加して、それを降順にしたインデックスで代用できるはずなので試されてみて下さい。

追記2

後は、フルテキストインデックス以外の条件である程度の件数に絞れるなら、existsによる条件に変更すれば、最初の条件の件数以上のアクセスは発生しないので、高速かもしれません。

SQL

1SELECT * FROM [TABLE_NAME] t 2WHERE PREF='大阪府' AND STORE='お店' 3 and exists( 4 SELECT 1 FROM [TABLE_NAME] 5 where AUTO_ID=t.AUTO_ID and CONTAINS(TITLE,'"*中古*"') 6 ) 7ORDER BY AUTO_ID DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

※インデックスは前述と同様

投稿2019/11/28 06:46

編集2019/11/29 05:42
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

SQL Server 実行計画の表示方法 でインデックスが有効に使われているか確認できます。

・現状の実行計画を確認
・インデックスをPREF, STORE, TITLE で用意して
・実行計画を確認
としては?

SQL Server の実行環境がないので試していませんが、

SQL

1SELECT * FROM [TABLE_NAME] 2where 3 PREF = '大阪府' 4AND STORE = 'お店' 5AND CONTAINS(TITLE,'"*中古*"') 6ORDER BY AUTO_ID DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

が実行できますか?

投稿2019/11/28 02:23

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ycy

2019/11/28 03:12

ご指摘いただいたSQLで試しに実行を行うと、単純なキーワード単独検索よりも遅くなってしまいます。 PREFなどの条件での該当一致する件数が多いためかと思います。 間違っているかもしれませんが、少数に絞り込みが出来る内容からWHERE区に入れていくべきかと思うのですが・・・ 試したところ・・より一層遅くなりました。
Orlofsky

2019/11/28 05:58

回答に提案したインデックスを設定してから、提示したSELECT文を実行しないと遅くなるはずです。 実行計画をご確認ください。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問