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

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

ただいまの
回答率

88.34%

MySQL(InnnoDB)でfind_in_setをインデックスの張れる記述方法に変えたい

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 4,641

cnx

score 19

前提・実現したいこと

MySQL(InnnoDB)でfind_in_setを使っていますが、パフォーマンスが出ません。
find_in_setではインデックスが張れないので、find_in_setと同じ条件指定が出来て
インデックスが張れる記述方法などを探しています。
インデックスの張り方も含めてアイデアをいただけるとありがたいです。

現状のSQL例

select id
from mytable
where
     find_in_set(model, @models)

modelは文字列です。
@modelsは毎回異なる値が設定されるので、set @models = 'A, B, C, D, E'といった指定を
SQLの前段で記述するようにSQL生成プログラムを作成してあります。

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

find_in_setなんて関数を使わずに、素直にINで書きましょう。

select id 
from mytable 
where 
     model IN ('a', 'b', 'c', 'd')

なお、可変個の文字列を正しくエスケープ/プリペアするのは少々面倒かもしれないので、そこにはご注意ください。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/05/25 10:42

    早速のアドバイスありがとうございます。
    model IN ('a', 'b', 'c', 'd')
    こちらの「'a', 'b', 'c', 'd'」を1つの変数として扱う表記は可能でしょうか。
    または1つの変数から展開して'a', 'b', 'c', 'd'に戻す方法がありますでしょうか。
    (もしも無ければコメント頂かなくても大丈夫です。その際は、SQLの吐き出しの際に直接Where句内に値を埋め込む仕様に変更して試してみます。)

    キャンセル

  • 2016/05/25 13:39

    横から失礼します。

    > こちらの「'a', 'b', 'c', 'd'」を1つの変数として扱う表記は可能でしょうか。

    一応、可能ではあります。

    MySQLのドキュメントに、以下のように説明があります。
    https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html

    > ユーザー変数は、データ値を提供するためのものです。これらは、テーブル名やデータベース名が想定されるコンテキストなどでの識別子または識別子の一部として、または SELECT などの予約語として、SQL ステートメントの中で直接使用することはできません。
    > (中略)
    > 識別子を提供するためにユーザー変数を使用できないというこの原則の例外が、あとから実行する準備されたステートメントとして使用するために文字列を構築している場合です。

    例えば、以下のような感じです。

     SET @s = CONCAT('select id from mytable where model in (', @models, ')');
     PREPARE stmt FROM @s;
     EXECUTE stmt;

    もっとも、この方法はお勧めできません。
    SQLインジェクションの原因となる可能性があるからです。
    http://nippondanji.blogspot.jp/2010/01/sql.html

    SQLインジェクションの可能性、という点では、
    > SQLの吐き出しの際に直接Where句内に値を埋め込む仕様

    も、同様です。

    お使いの環境が不明なので具体的な提案はできませんが、
    バインド変数を使用する方式に変更した上で、SQLを呼び出すアプリケーション側で、
    IN句に指定する値の数だけパラメータマーカー("?"のこと)の数を動的に増やすよう、実装してやるのが良いと思います。
    https://dev.mysql.com/doc/refman/5.6/ja/prepare.html

    例えば、
     String sql = 'select id from mytable where model in (%s)';

    という文字列を用意しておき、"%s"部分を指定したい値の数だけ ?, ?, ?, ... と変化させるイメージです。

    キャンセル

  • 2016/05/25 14:52

    丁寧な解説ありがとうございました。
    環境のご説明をさせて頂きますと、

    Windows環境のバッチファイルで条件を入力

    条件に応じたSQLファイルをバッチ内で生成

    WinSCP経由(証明書のみ受け入れる接続設定)でリモートのCentOSに接続
    (接続元IP制限も実施、2段階でその先の別のCentOSインスタンスに接続)

    そこでWinSCPスクリプト機能でMySQLに接続、生成されたSQLをアップロード&実行

    結果をダウンロードして終了

    という流れになっております。
    Webを介してPHPなどが動き、SQLを実行するような環境とは異なっております。

    頂いた情報を参考に修正と確認をしてみます。

    キャンセル

0

mytable.model の型はSET型ですか?
SET型でなければSET型にしたらいかがですか?(手遅れでなければ)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/05/25 12:25

    アドバイスありがとうございます。
    残念ながら、全てTEXT型で作られてしまっていて変更が出来ません。因みに、model列にはこの場合、A, B, C, D, Eの何れか1つだけが入っているようなイメージです。

    キャンセル

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

  • ただいまの回答率 88.34%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る