SQLなら俺に訊け [無断転載禁止]©2ch.net (457ﾚｽ)
上下前次1-新
抽出解除 必死ﾁｪｯｶｰ(本家) (べ) 自ID ﾚｽ栞 あぼーん
ﾘﾛｰﾄﾞ規制です｡10分ほどで解除するので､他のﾌﾞﾗｳｻﾞへ避難してください｡
155: デフォルトの名無しさん [sage] 2021/12/24(金) 16:39:20.24 ID:unjC7EWw(1/5) AAS
複数のカラムのどれかに該当文字を含む行を知りたいが、
どのカラムだったかで区別したい時に、一発でやる方法ってある？
具体的には以下(２発)を一発にしたい。
SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION SELECT 1,target FROM sometable WHERE col1 MATCH 'str' AND col0 NOT MATCH 'str';
例えば、「どのORに当たったか」を教えてくれるスカラー関数 hitplace() があったとすると、
SELECT hitplace(),target FROM sometable WHERE col0 MATCH 'str' OR col1 MATCH 'str';
に出来るのだけど。
環境はPHP+SQLite。
出力は配列の配列、 [[col0に当たった行],[col1に当たった行]] の形式で、重複はなし。
この形式にサクッと変換出来るのなら、読み出した形式は上記とは違っても可。
157(1): デフォルトの名無しさん [sage] 2021/12/24(金) 19:31:42.64 ID:unjC7EWw(2/5) AAS
お早い回答ありがとう。
もっと色々試すが取り急ぎ。
新案1：
SELECT CASE WHEN col0 LIKE 'str' THEN 0 WHEN col1 LIKE 'str' THEN 1 ELSE -1 END AS col,target FROM sometable WHERE col>=0;
explainでは32、他だと文法エラーらしいがSQLiteだと通る。(参考 外部ﾘﾝｸ:rainbow-engine.com
explain query plan では
0|0|0|SCAN TABLE tags_bulk VIRTUAL TABLE INDEX 0:
新案2：
SELECT col,target FROM (SELECT CASE WHEN col0 LIKE 'str' THEN 0 WHEN col1 LIKE 'str' THEN 1 ELSE -1 END AS col,* FROM sometable) WHERE col>=0;
他ではこう書けと言われているもの。
explainでは32で、見た目中身も同じ。explain query plan も全く同じ。
158: デフォルトの名無しさん [sage] 2021/12/24(金) 19:32:06.67 ID:unjC7EWw(3/5) AAS
元：
SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION SELECT 1,target FROM sometable WHERE col1 MATCH 'str' AND col0 NOT MATCH 'str';
explainでは同じく32だが、見た目は違う。が、読み方が分からない。
explain query planでは以下。見た目通り2周している。
1|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 9:
2|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 11:
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
MATCHはCASEの中では使えないらしくLIKEにしている。
(Error: unable to use function MATCH in the requested contextと出る。)
MATCHだとインデックスが使える分速いはずだが2周する分遅くなるのでどうなるか。
DBやアプリ構造の改善案は今のところ思いつかない。(変更は可能)
カラムはそれぞれ別の管理なので、先に合わせる事は出来ない。
159: デフォルトの名無しさん [sage] 2021/12/24(金) 21:34:31.41 ID:unjC7EWw(4/5) AAS
新案3: UNION ALL
SELECT 0,target FROM sometable WHERE col0 MATCH 'str' UNION ALL SELECT 1,target FROM sometable WHERE col1 MATCH 'str' AND col0 NOT MATCH 'str';
元はUNIONにB-TREEが必要でこの分無駄だった。
explainは23と軽くなった。explain query plan は以下。
1|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 9:
2|0|0|SCAN TABLE sometable VIRTUAL TABLE INDEX 11:
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
単発で別々にクエリすればUNION ALL 分も取れるが、単に追加してるだけなのでPHP側で結合するよりは速いはず。
今のところこの辺か。他に何かあればよろしく。
とりあえず実装して試すが、差が出るほどDBの中身が埋まるまでにはだいぶかかるとは思う。
161: デフォルトの名無しさん [sage] 2021/12/24(金) 22:28:50.21 ID:unjC7EWw(5/5) AAS
>>160160(1): デフォルトの名無しさん [sage] 2021/12/24(金) 21:37:57.02 ID:cMhJNtck(2/2) AAS
>>157
その案はどちらもテーブルスキャンになってインデックス使わない
SELECT句のCASE式とは別にWHERE句に本来の条件を書く
DB構造の話はカラムとして持つべきデータじゃなくて
子テーブルの行として持つべきデータかと思ったんだけど
全文検索でいずれかのカラムに検索キーワードが含まれてる行を
どのカラムかという情報も含めて抽出する用途なんであれば
そのままでいいんじゃないかと思う
> その案はどちらもテーブルスキャンになってインデックス使わない
そうだが、今回はこれで仕様的にも問題ない。
俺が勘違いしてたのもあって言葉が混乱しているが、
SQLiteのFTSは全文検索仕様で、通常のインデックスは作成出来ない。
FTSで「インデックス」と言われてるのは全文検索用のキーワードインデックスで、
つまりMATCHがLIKEに比べて糞速いだけで、常に全row検索する仕様のようだ。
外部ﾘﾝｸ[html]:sqlite.org
（俺がFTSの「インデックス」をここでもそのままインデックスと表記したのが不味かった。
FTS用のテーブルでは通常のインデックスでの単rowからの検索は出来ない。
《rowidだけは使えるらしいが、使っても explain query plan では scan と表示された》)
> 子テーブルの行として持つべきデータかと思ったんだけど
あーなるほど、勘がいいね。
実は元々そういう構造になっていたのだが、
下部構造のサマリを作ってしまった方が楽だからそうしようとしていて、
col0は上部から与える名前、col1は下部のそれぞれの中身の寄せ集めになってる。
だからcol0を下部構造の各行のcol1相当部分に対して混ぜ込めば、下部構造で一発クエリ出来る。
そして元々そうしていたのだが、他の都合上、管理が面倒なので変更しようとしているところ。
しかしよく分かったね。まあ妙な事をしてるからか？
まあとにかくありがとう。
今回は下部構造のサマリなので、常に全文検索で問題ない。
言葉が混乱しててごめん。
上下前次1-新書関写板覧索設栞歴
ｽﾚ情報 赤ﾚｽ抽出 画像ﾚｽ抽出 歴の未読ｽﾚ AAｻﾑﾈｲﾙ
ぬこの手 ぬこTOP 0.026s