LIKE検索におけるINDEXの有効化について
- テーブル同士のLIKE検索（前方一致）を行った場合、INDEXが有効になりません。
- LIKE検索文字列を固定にした場合は、INDEXが有効になります。
- テーブル同士のLIKE（前方一致）でINDEXを有効にする手段をご教授頂けないでしょうか。
- ベストアンサー
前方一致が動的に変更される場合にINDEXは有効になりません。
いつもお世話になっております。 テーブル同士のLIKE検索（前方一致）を行った場合、 INDEXが有効になりません。 LIKE検索文字列を固定にした場合は、INDEXが有効 になります。 テーブル同士のLIKE（前方一致）でINDEXを 有効にする手段をご教授頂けないでしょうか。 以下、実行結果です。 (1)LIKE検索文字列が固定 ---------------------------------------------------------------------------- EXPLAIN SELECT * FROM wk_dss_data WHERE sys_id LIKE 'M004964%'; ---------------------------------------------------------------------------- Index Scan using i_wk_dss_data_02 on wk_dss_data (cost=0.00..5.25 rows=1 width=311) Index Cond: (((sys_id)::text >= 'M004964'::character varying) AND ((sys_id)::text < 'M004965'::character varying)) Filter: ((sys_id)::text ~~ 'M004964%'::text) (2)テーブル同士のLIKE検索 ---------------------------------------------------------------------------- EXPLAIN SELECT wk_dss_data.comp_id, wk_dss_data.user_id FROM wk_dss_data, t_update WHERE wk_dss_data.sys_id LIKE t_update.sys_id||'%'; ---------------------------------------------------------------------------- Nested Loop (cost=157.00..12505636.00 rows=2500000 width=20) Join Filter: (("outer".sys_id)::text ~~ (("inner".sys_id)::text || '%'::text) ) -> Seq Scan on wk_dss_data (cost=0.00..5479.00 rows=100000 width=31) -> Materialize (cost=157.00..207.00 rows=5000 width=11) -> Seq Scan on t_update (cost=0.00..157.00 rows=5000 width=11)
- yamataro25
- お礼率75% (12/16)
- PostgreSQL
- 回答数2
- ありがとう数2
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
- ベストアンサー
私も、以前同じ現象になって調べました。 PostgreSQLでは、like検索には通常のindexは使われないようです。 versionは、8.0.xでした。 likeを使うようにインデックスを作成するにはvarchar_pattern_opsオプションを付けます。 <code> CREATE INDEX test_index ON test_table (col varchar_pattern_ops); </code> 詳しくは参考URLを見てみてください。
その他の回答 (1)
私はこれで解決できたのですが、別の問題なのでしょうか。 あとはPostgreSQLのオプティマイザですね。 Alanizeオプションをつけてvacuumを実行してみてはどうでしょうか。
関連するQ&A
- INDEXの仕様
PostgreSQL8.1.1（RedHatLinux）にて テーブルのtext列に対してindexを作成しました。 作成した列に対して、LIKE検索を行っております。 しかし、「EXPLAIN」を利用してSQLの実行計画を見たんですが、 「Seq Scan」からしか始まらず、 「Index Scan」という文字が見当たりません。 PostgreSQLにてindexを張った列に対するLIKE検索では、 indexは使用されないのでしょうか？
- ベストアンサー
- PostgreSQL
- インデックスについて
テーブルは2つ ・offacc_siwakedata ・off_projectmster とあり、それぞれ下記インデックスを張っております。 create index siwakedata_index ON offacc_siwakedata( siwakedata_date, siwakedata_hdcd, siwakedata_cd, siwakedata_kamokucode, siwakedata_projectcode, siwakedata_exzankada, siwakedata_default, siwakedata_compcd, siwakedata_del ); CREATE INDEX projectmster_index ON off_projectmster( projectmster_cd, projectmster_del, projectmster_compcd ); ２つのテーブルを結合させる下記SQLを発行 explain select offacc_siwakedata.*, off_projectmster.projectmster_name from offacc_siwakedata,off_projectmster where off_projectmster.projectmster_cd=siwakedata_projectcode and off_projectmster.projectmster_del='1' and off_projectmster.projectmster_compcd='200' and siwakedata_compcd='200' and siwakedata_del='1' and siwakedata_default='1' and siwakedata_kamokucode='26300' and siwakedata_exzankada<>0 order by siwakedata_date, siwakedata_hdcd, siwakedata_cd ; explainするとoff_projectmsterのインデックスが有効になっていません。 何ででしょうか？ 教えてください。 宜しくお願いします。 "Nested Loop (cost=0.00..17438.04 rows=1 width=1307)" " Join Filter: ((off_projectmster.projectmster_cd)::text = (offacc_siwakedata.siwakedata_projectcode)::text)" " -> Index Scan using siwakedata_index on offacc_siwakedata (cost=0.00..17436.98 rows=1 width=1244)" " Index Cond: (((siwakedata_kamokucode)::text = '26300'::text) AND ((siwakedata_default)::text = '1'::text) AND ((siwakedata_compcd)::text = '200'::text) AND (siwakedata_del = 1))" " Filter: (siwakedata_exzankada <> 0::numeric)" " -> Seq Scan on off_projectmster (cost=0.00..1.04 rows=1 width=96)" " Filter: ((projectmster_del = 1) AND ((projectmster_compcd)::text = '200'::text))"
- 締切済み
- PostgreSQL
- 速度差の理由を教えてください。
データベースの構造とインデックス、検索クエリー 全て同じ条件でテストサーバと実際のサーバで 検索を行っているのですが以下のような結果がでます。 なぜ以下の様に10倍まで差が生まれるのかを 教えていただければと思っております。 違う箇所としては、サーバのスペックですが 実際のサーバのCPU：Xeon 3.2GHz メモリ：2GB テストサーバのCPU：Pentium4 3.2GHz メモリ：2GB となります。 //テストサーバでの結果 Limit (cost=99427.05..99427.25 rows=4 width=112) (actual time=4022.29..4022.31 rows=4 loops=1) -> Unique (cost=99427.05..99438.37 rows=226 width=112) (actual time=4022.29..4022.30 rows=5 loops=1) -> Sort (cost=99427.05..99432.71 rows=2265 width=112) (actual time=4022.29..4022.29 rows=6 loops=1) Sort Key: s.saorderid, s.saserialid, o.odlastupdate -> Merge Join (cost=96997.70..99300.85 rows=2265 width=112) (actual time=3363.93..4022.11 rows=40 loops=1) Merge Cond: ("outer".odid = "inner".saorderid) -> Sort (cost=8054.50..8059.58 rows=2033 width=68) (actual time=13.52..13.55 rows=38 loops=1) Sort Key: o.odid -> Index Scan using order1_odctmid_index on order1 o (cost=0.00..7942.81 rows=2033 width=68) (actual time=13.13..13.47 rows=38 loops=1) Index Cond: (odctmid = 3403::bigint) -> Sort (cost=88943.20..90075.54 rows=452935 width=44) (actual time=3344.05..3688.13 rows=452002 loops=1) Sort Key: s.saorderid -> Seq Scan on sale s (cost=0.00..12792.35 rows=452935 width=44) (actual time=0.02..756.03 rows=452935 loops=1) Total runtime: 4050.02 msec //実際の本番サーバでの結果 Limit (cost=73933.37..73933.39 rows=1 width=128) (actual time=43102.58..43102.59 rows=4 loops=1) -> Unique (cost=73933.37..73933.39 rows=1 width=128) (actual time=43102.58..43102.59 rows=5 loops=1) -> Sort (cost=73933.37..73933.38 rows=5 width=128) (actual time=43102.57..43102.58 rows=6 loops=1) Sort Key: s.saorderid, s.saserialid, o.odlastupdate -> Nested Loop (cost=0.00..73933.32 rows=5 width=128) (actual time=998.20..43102.38 rows=40 loops=1) Join Filter: ("outer".odid = "inner".saorderid) -> Index Scan using order1_odctmid_index on order1 o (cost=0.00..18.12 rows=4 width=68) (actual time=6.01..6.99 rows=38 loops=1) Index Cond: (odctmid = 3403::bigint) -> Seq Scan on sale s (cost=0.00..12797.54 rows=448254 width=60) (actual time=0.01..756.51 rows=455437 loops=38) Total runtime: 43102.69 msec もしクエリー文もあった方がよければ お知らせ下さい。 すみませんが、どなたか教えて頂ければ と思います。よろしくお願い致します。
- ベストアンサー
- PostgreSQL
- indexを使おうとしない間違ったcost計算
postgres 7.2.4で質問させてください。 以下のような構成のテーブルの抽出をしようとしています。 テーブル名 ：t_name カラム名 ：k1、k2、k3 インデックス：t_name_idx テーブルt_nameの全体件数は約70万件で、上記のSQLから期待 される抽出結果件数は約1万5千件です。t_nameにはk3のみを対象としたインデックス、t_name_idxが作成してあります。次のようなSQLを実行した際に、シーケンス検索になり応答までに６秒ほどかかってしまいます。 select k1,k2 from t_name where k3 in ("100","200"); explain analyzeで実行計画を見ると (cost=26.29..15304.62 rows=523 width=104) Total runtime: 6427.32 msec です。シーケンスをSET enable_seqscan TO off; で使用しないようにして、強制的にt_name_idxを使うと次のような結果になります。 (cost=0.00..42009.05 rows=523 width=104) Total runtime: 423.81 msec 実際にはindexを使用したほうが10倍以上も速いのに、プランナはシーケンス検索のほうがコストが小さいと判断しています。何故このようになってしまうのでしょうか？VACUUM ANALYZEを行っても結果は変わりませんでした。 私のイメージでは、index検索のほうがコストが小さく計算されて、それを使うべき。というイメージなのですが、考え方自体が間違っているのでしょうか？ 原因を調べ始めて１週間経ち、行き詰っています。 どなたかヒントだけでもいいので教えてください。
- ベストアンサー
- PostgreSQL
- ■検索時にINDEX(インデックス)がどう役立つのか？
いつもお世話になっております。 MySQLのカテゴリにて質問をさせて頂きますが、このことに意味はありません。 SQLにおける、INDEXの考え方を知ることができれば良いなと思っております。 SQLについては初心者ですので、分かりやすく教えて頂けると助かります。 では、本題へ。 ------------- あるデータテーブルの検索速度を上げる方法として、 INDEXを設定することもその１つに挙げられるかと思いますが、 あるテーブルにおける、ある列（フィールド）に対しINDEXを設定した場合、 検索時、それはどう利用されるのでしょうか、というのが質問です。 例えば、 会員データベース ・会員番号：id ・年齢：age ※その他、複数のフィールドを持つ、とする。 上記の会員データベースにおいて、 CREATE INDEX idx ON tbl_member(id); という風に、tbl_memberテーブルに対し、 会員番号（id）をINDEXに設定したとします、 idは唯一無二の（ユニークな）データです。 この会員テーブルにおいて、 年齢（age)フィールドをもとに、40歳以上の会員を抽出（検索）したい場合、 INDEX（idが設定されている）はこの検索において、 どういう仕組みで活かされることになるのでしょうか？ ageと無関係のidが、どう役に立つのか分からずにいる、ということです。 ネット上で色々調べたのですが、 あるフィールドをINDEXに設定した場合に、 それが検索時にどう活かされるのかについて説明されているページを 私は見つけることができませんでしたので、質問させて頂きました。 どうぞ、宜しくお願い致します。
- ベストアンサー
- MySQL
- SQL 完全に一致したデータを検索抽出
SQLのテーブルから完全に一致したデータを抽出するにはどうすればよいでしょうか。 仮に、dayとweatherの２つのカラムでテーブルを作成 <?php /* 検索フォーム */ ?> <form action = "index.php" method="post"> <table> <tbody> <tr> <th>月</th> <th>天気</th> </tr> <tr> <td><input type="text" name="day"></td> <td><input type="text" name="weather"></td> </tr> </tbody> </table> <input type="submit" name="submit" value="検索"> </form> <?php /* 検索 */ ?> <?php ～略～（SQLを呼び出す） $pdo = connect(); $st = $pdo->query("SELECT * FROM table WHERE day LIKE '%" . $_POST["day"] . "%' ); $word = $st->fetchAll(); ?> とします。 カラム「day」の中に「2」「12」「25」のデータが入っていたとします。 この場合、検索フォームで「2」と入力して検索すると 「2」「12」「25」全てのデータが抽出されます。 そうではなく、「2」のデータだけが抽出されるにはどうしたらよいでしょうか？ 検索フォームを作る時は、上記のLIKEを使ったものしか知らないので、困っております。 何卒よろしくお願いします。
- ベストアンサー
- MySQL
- mysqlのインデックスについて質問です。
mysqlのインデックスについて質問です。 http://archiva.jp/web/server-side/sql_02.html に、 『mysqlでは1つのクエリ実行で、1つのテーブルにつき1つのインデックスしか使用できない』 とあります。 よく以下のようなテーブル定義を見かけますが、 CREATE TABLE IF NOT EXISTS `data` ( `id` int(10) NOT NULL default '0', `user_id` int(10) NOT NULL default '0', `file_id` int(10) NOT NULL default '0', `name` text collate utf8_unicode_ci NOT NULL default '', PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; idとuser_idを条件にするクエリであれば、上記制約を満たすため、 KEY `id_user_id_idx` (`id`,`user_id`) の複合クエリを定義すれば良いのでしょうか？ また、idとuser_idとfile_idを条件にするクエリも考慮に入れた場合、 テーブル定義は以下のようにするのでしょうか？ CREATE TABLE IF NOT EXISTS `data` ( `id` int(10) NOT NULL default '0', `user_id` int(10) NOT NULL default '0', `file_id` int(10) NOT NULL default '0', `name` text collate utf8_unicode_ci NOT NULL default '', PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `id_user_id_idx` (`id`,`user_id`), KEY `id_user_id_file_id_idx` (`id`,`user_id`,`file_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 最後に、create table 時のインデックスの指定は、 INDEX `user_id` (`user_id`), のようにもできますが、INDEXとKEYの違いは何かあるのでしょうか。
- ベストアンサー
- MySQL
- MySQLでFullTextインデックスの再構築
現在Webアプリを制作しています。 そこで検索機能を作成するにあたり FullTextで全文検索を行う処理を考えています。 Like文を使って検索するより速いらしいのですが、 insert/update文を実行しデータベースにデータを挿入/更新する際 FullTextで全文検索を行う場合だとインデックスを再構築する必要が あるみたいです。 http://melrose.jugem.cc/?eid=323 このインデックスの再構築についてよく分からないのですが、 上記のサイトではインデックスの再構築には REPAIR TABLE [テーブル名] QUICK; を実行とありました。 データの挿入/削除 処理後の検索インデックス修復には innsert 又は update文の直後にREPAIR TABLE [テーブル名] QUICK; を 実行するという事でいいのでしょうか？
- ベストアンサー
- MySQL
- Index の使い方、where a.カラム1 LIKE CONCAT
Index の使い方、where a.カラム1 LIKE CONCAT(b.カラム2,'%') お世話になります。 テーブルA： id phone ----------------------------------- 1 819011112222 2 819022223333 3 81312345678 4 651112222 5 8699998888 6 819011112222 テーブルB： id Name Pref --------------------------------------------------- 1 Japan-1 8131234 2 JP-Mobile 8190 3 China-1 869 4 China-2 868 5 Japan-2 813 6 Japan 81 7 Singapore 65 このようなテーブルで、テーブルBはレコード数が数千件あります。 数千件でしたし、データを取るのに遅いとは思いますが、 なんとか耐えらえた範囲なので下記のように実行しておりました。 select a.*,b.Name,b.Pref FROM テーブルA a, テーブルB b where a.phone LIKE CONCAT(Pref,'%') group by a.id; 結果 id phone Name ---------------------------------------------------- 1 819011112222 JP-Mobile 2 819022223333 JP-Mobile 3 81312345678 Japan-2 4 651112222 Singapore 5 8699998888 China-1 5 862223333 China-2 6 819011112222 JP-Mobile テーブルB Prefに対してIndexを作成していますが、どうも作動していないようです。 Indexに関してよく理解できていなかったので、気にしていなかったのですが、今回早さを比べる為に Indexあり、なしで試しみても大して変っていませんでした。 今のところ、Indexを使えていなくても問題ないのですが、今後テーブルBのデータが数万件に増えてしまい、今までのやり方が通用しません。 効果的に使用する方法をアドバイス頂けませんでしょうか。 よろしくお願いします。
- 締切済み
- MySQL
- CSS z-index？ ブラウザによって表示結果が異なってしまう。
<html> <head> <style type="text/css"> #bg{ width: 500px; background: #f8dce0; } #left{ float: left; z-index: 1; } #right{ float: right; z-index: 2; } #ans1_0,#ans1_1,#ans1_2{ color: #FF0000; } </style> </head> <body> <p id="bg"> test<br> <span id="left"> a<br> b<br> c<br> </span> <span id="right"> 1<br> 2<br> 3<br> </span> </p> </body> </html> 上記をIE,FireFox2,Operaで試してみると、FireFox2のみ期待した表示が得られません。 何が悪いのでしょうか？
- ベストアンサー
- HTML
お礼
DQ9さん お世話になりました。 明確な回答有難うございます。 インデックスを再作成し再度実行してみます。
補足
DQさん お世話になります。 以下のインデックスを作成し実行しましたが インデックスが使われませんでした。 ほかに考慮点ございましたらご教授頂けると 幸いです。 以下、インデックス作成 create index i_test ON t_update (sys_id varchar_pattern_ops); create index i_test2 ON wk_dss_data (sys_id varchar_pattern_ops);