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

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

ただいまの
回答率

90.51%

  • MySQL

    7002questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    3016questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • データベース

    837questions

    データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

  • データベース設計

    182questions

    データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

  • パフォーマンス

    25questions

    コード効率の向上や計算に関する質問には、このタグを使ってください。

MySQL 大容量データ INDEX 前方一致のLIKEが効かない

解決済

回答 8

投稿 編集

  • 評価
  • クリップ 5
  • VIEW 3,763

shin_tera

score 21

前提・実現したいこと

以下の状況において、LIKE の前方一致によるINDEXが効かず、性能が出なくて困っております。。。
INDEXが効かない原因と対応策を教えて頂けないでしょうか。

<状況>
1:CREATE TABLEテーブルは以下です。

CREATE TABLE `tb_test_data` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `col1` bigint(20) NOT NULL DEFAULT '0',
 `col2` int(11) NOT NULL DEFAULT '0',
 `col3` float NOT NULL DEFAULT '0',
 `col4` float NOT NULL DEFAULT '0',
 `col5` int(3) NOT NULL DEFAULT '0',
 `col6` int(11) NOT NULL DEFAULT '0',
 `col7` bigint(20) NOT NULL,
 `url` text,
 `date` date NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY UNI_col1_col5( col1, col5 ),
 KEY `IX_col2` (`col7`, col2),
 KEY `IX_col3` (`col7`, col3),
 KEY `IX_col4` (`col7`, col4),
 KEY `IX_col5_col1` (`col7`, col5, col1),
 KEY `IX_col6` (`col7`, col6),
 KEY `IX_col1_col5` (`col7`, col1, col5),
 KEY `IX_date` (`col7`, date),
 KEY `IX_url` ( url(255) )
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2:上記テーブルは更新型テーブル。更新頻度は毎秒100件くらいのDELETE / INSERTを24時間実施。
(MyISAMのため、参照するとテーブルロックになり、更新はされません)
登録順はcol1をキーにcol5の分だけ削除し、削除したcol1の値でcol5の分だけ登録を行います。

3:上記テーブルは現在、1.5億レコード存在します。AUTO_INCREMENTの値は4.5億です。

4:URL に INDEXを効かせたいSQLは以下のSQLです。

SELECT    T1.*
FROM    tb_test_data T1
WHERE    T1.col7    =    123456
    AND    T1.url        like 'http://abc.jp/xxx/xxxx/xxx%'
ORDER BY col1
Limit 0, 100

5:実行計画

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | T1 | range | IX_col1,IX_col2,IX_col3,IX_col5_col1,IX_col6,IX_col1_col5,IX_date,IX_url | IX_col1_col5 | 8 | NULL | 1157468 | Using index condition; Using where 


※上記の実行計画は「http://abc.jp/xxx/xxxx/xxx%」の部分を68文字のURLを指定し、このURLだけで1件の表示になる値で実行計画を出しています。そのため、理想の実行計画としては、
refに「IX_url」が表示され、rows項目も1となる事を想定していました。
※仮に「http://abc.jp/xxx/xxxx/xxx%」とした場合、データが存在しないため、
ref項目に「IX_url」が表示され、rows項目も1となります。

6:その他注意事項
・URL項目に対して、Likeではなく、=による絞り込みはNGとなります。
・URLを階層別にハッシュ化して、=による絞り込みもNGとなります。
・URLを1文字ずつずらしてハッシュ化して、=による絞り込みはOKですが、
INDEXが最大数(64個)を超えてしまうため、出来ないと考えてます。
・MATCH AGAINSTはOKですが、この更新型テーブルにFULLTEXT INDEXを貼って効果があるかは、現在調査中です。

発生している問題・エラーメッセージ

USE INDEX ( IX_url )やUSE KEY( IX_url )を利用しても、実行計画のref項目に「IX_url」は表示されず、
実際に実行してもものすごく時間がかかる(10分経っても終わらない)状況です。
これを数秒以内に結果が返ってくる形にしたいと考えています。

該当のソースコード

上記SQL参照

試したこと

上記説明参照

補足情報(言語/FW/ツール等のバージョンなど)

MySQLのバージョンは5.6.22
phpMyAdminのバージョンは4.5.1(ちょっと古い)

実行計画や速度が遅く感じると判断した際の動作環境は
phpMyAdmin上での実行結果になります。

追記

check table tb_test_data;
を実行した場合、2時間経っても終わらないため、実施していません。

check table tb_test_data fast quick;
にて、テーブルの状態を確認していますが、
statusは「Table is already up to date」の状態で、問題ない認識です。

show table status like 'tb_test_data%';
にて、断片化は確認をしていて、0.6%程度の断片化率です。
(該当テーブルのURL以外の指定における性能劣化は見られていません。)

追記2

検索用URL(100文字等)を作ってINDEXを貼って検索するのは、個人的には良いのですが、
仕様決定者的にはNGでした。。

追記3

index(col7,col1,url(200))の追加案については、
col7での絞込件数≒実際の絞込件数(urlによる絞り込みがほとんどない)
であれば、Limitが効くため、性能は良いですが、
検索結果が「1件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。

index(col1,col7,url(200))の追加案については、
全体の件数≒col7での絞込件数≒実際の絞込件数(col7,urlによる絞り込みがほとんどない)
であれば、Limitが効くため、性能は良いですが、
検索結果が「1件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。

なお、上記2つのINDEXでは、URLのINDEXには届かないため、1件抽出する場合、即レスポンスにならないです。

INDEXさえ効けば、検索結果が1件の場合、即終わるハズなのですが、
実行計画の時点で効かない原因を知りたいです。。

追記4

InnoDBについて

InnoDB化した場合、300万の登録に60分経っても終わらなかったため、INDEXが効くかどうかは分かりません。
別の仕様(毎秒100件の削除・登録)が不可であるため、InnoDB化はできません。
(MyISAMであれば、300万の登録に7分で終わります)
InnoDBは統計情報がきれいなはずなので、InnoDBであれば、確かに効く気はしています。

追記5

INDEXが効いたSQLと効かないSQLがありましたので、情報共有いたします。
”存在しないURL”を指定すると、基本的に、INDEXが効きいて、1秒以内のレスポンスになりました。
ただし、"存在しないURL"が50文字以上となると、INDEXが効かず、300秒くらいのレスポンスになってしまいました。。

MySQL5.6.22のバグなんじゃないかと思い始めてます。。

追記6

AkiraPenguin 様の回答により、解決いたしました!
ありがとうございます!!!!!

なお、追記5の問題も、ちょうど50文字目が「_」となっておりました。
「_」を含むURLは300万件存在するため、「_」の後に存在しないURLを置いていても、
それはINDEXが効きません。。。当然ですね。。。

たくさんの閲覧・回答ありがとうございました!

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 8

checkベストアンサー

+6

検索用の URL には '%', '_' などは含んでいませんか?
上記を含んでいる場合、適切にエスケープしないと文字列の途中にワイルドカードがある状態となり、極端にパフォーマンスが低下すると思われます。

また、'like' を 'like binary' としてはどうでしょうか?
多少レスポンスが良くなるのではと思います。

メモリ量が原因かどうか、同一データを仮想マシン上でメモリを増減させて検索時間を確認すると良いかもしれません。
結局、メモリの補強、DB用ストレージをSSDにするなどの対策が必要かもしれません。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/14 12:55

    おおおおおおおおおおおおおおおおおおおおおおおおおおおお!!!!!
    ありがとうございます!!!

    > 検索用の URL には '%', '_' などは含んでいませんか?
    ⇒%は含めていませんが、「_」が含まれておりました!!

    急いで確認します。ありがとうございます!!

    キャンセル

  • 2016/09/15 11:56

    おかげさまで、解決いたしました!!
    本当にありがとうございます!!

    キャンセル

  • 2016/09/15 19:57

    いえいえ。お互い様です。
    同じ問題で困っている方を見かけたら助けてあげて下さい。

    キャンセル

+3

ANALYZE TABLE および OPTIMIZE TABLE は、定期的に実行していますか?
https://dev.mysql.com/doc/refman/5.6/ja/optimizing-queries-myisam.html

MySQL がクエリーをより適切に最適化できるようにするには、テーブルにデータがロードされたあとに、それに対して ANALYZE TABLE を使用するか、または myisamchk --analyze を実行します。

定期的に OPTIMIZE TABLE を使用して、動的フォーマット MyISAM テーブルの断片化を防ぎます。


また、

USE INDEX ( IX_url )やUSE KEY( IX_url )を利用しても、実行計画のref項目に「IX_url」は表示されず

とのことですが、FORCE INDEX 構文なら効くかもしれません(これも確実ではありませんが)。
https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html

USE INDEX (index_list) と同様の機能を持つが、テーブルスキャンが非常に負荷が大きいと見なされる点が追加された FORCE INDEX を使用することもできます。つまり、テーブルスキャンは、指定されたインデックスのいずれかを使用してテーブル内の行を検索する方法がない場合にのみ使用されます。

 追記

ANALYZE TABLEやOPTIMIZE TABLEは1億レコードの場合、処理が終わらないため、
実行しておりません。
その代り、定期的(2月に1回程度)に、別テーブルへのデータ移行&プログラム修正(参照先修正)を2日かけて行っています。

となると、相当な程度、統計情報が狂っている可能性があります。

更新頻度は毎秒100件くらいのDELETE / INSERTを24時間実施。

ということは 2ヶ月で約5億レコードの DELETE / INSERT が発生するため、
データ移行をする頃には、前回のデータ移行時からレコードが丸ごと入れ替わっていてもおかしくないからです。

check table tb_test_data fast quick; 
にて、テーブルの状態を確認していますが、 
statusは「Table is already up to date」の状態で、問題ない認識です。

とのことですが、
「(統計情報が)問題ない認識」
という意味なら、これは誤りです。

FAST オプションを指定すると、統計情報の更新は行なわないためです。
https://dev.mysql.com/doc/refman/5.6/ja/check-table.html

FAST    正しく閉じられていないテーブルのみを検査します。


可能であれば、ストレージエンジンを InnoDB に変更することも検討してみてください。

InnoDB であれば統計情報は自動的に更新されます(※1)し、

ALTER TABLE tb_test_data ENGINE InnoDB;


という SQL で OPTIMIZE TABLE と同じことを、テーブルロック無しに実現できる(※2)からです。

※1 http://nippondanji.blogspot.jp/2010/09/innodb.html

InnoDBの場合、ANALYZE TABLEは不要である。なぜなら、InnoDBが自発的に統計情報を更新するからだ。

※2 同上

InnoDBにはOPTIMIZE TABLEに相当する機能は実装されておらず、代わりにALTER TABLEが実行される。

※2 https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html

並列 DML ではあるが、テーブルコピーが引き続き必要
(中略)
「null」 ALTER TABLE ... ENGINE=INNODB ステートメントを使用したテーブルの再構築

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/06 13:18 編集

    ANALYZE TABLEやOPTIMIZE TABLEは1億レコードの場合、処理が終わらないため、
    実行しておりません。
    その代り、定期的(2月に1回程度)に、別テーブルへのデータ移行&プログラム修正(参照先修正)を2日かけて行っています。

    FORCE INDEXについて、ありがとうございます。
    FORCE INDEXでも同じように、実行計画ではref項目はNULLとなってしまい、
    「IX_url」は現れませんでした。実際に実行しても処理が終わらない状況でした。。。

    キャンセル

  • 2016/09/06 15:35

    返信ありがとうございます。

    回答欄に追記させていただきました。
    確認願います。

    キャンセル

  • 2016/09/06 17:44

    ご確認・ご回答ありがとうございます。

    > 相当な程度、統計情報が狂っている可能性があります。
    ⇒ご指摘の通り、狂っている可能性が高いと思いますが、
     現時点で狂ってる確証を得るための調べる術がなく。。。
     なお、本現象(URLの前方一致のINDEXが効かない)については、
     (データ移行前はもちろんですが)データ移行直後も発生しております。
     そのことから、統計情報の狂いではないと推測(調べられない)してます。

    >「(統計情報が)問題ない認識」
    ⇒すみません。「fast quickの結果としては問題ない認識」となります。
     そのため、「正しく閉じられているテーブル」である認識です。

    >ストレージエンジンを InnoDB に変更することも検討してみてください。
    ⇒ありがとうございます。
     他のテーブルも全てMyISAMであり、テーブルロックの方が(別の要件で)都合が良く、影響調査が大変であるため、
     InnoDBへの切替は躊躇していましたが、
     本問題の解決策として、ちょっとやってみます。
     分かり次第、質問に追記いたします。

    キャンセル

  • 2016/09/13 07:58

    実行計画を見る限り複数のindex張りすぎで起きている気がする。

    キャンセル

  • 2016/09/14 19:53

    超絶な横槍ですが、
    ・DBエンジンはMyISAM
    ・更新系テーブル
    ・貼られたインデックス数が多い
    ・データ量も多い


    遅かれ早かれ抜本的な対策を取らないといけないでしょうね・・・
    ぎりぎりになって慌てるより、
    可能であれば少しずつでも舵切りを進めた方が良さそう^^;

    キャンセル

+1

まぁ第一に1.5億データとなるとパフォーマンス的にかなり厳しいでしょう
nosql系の処理に切り替えた方がよいかもしれません。

たしかtextにはインデックスって貼れないですよね?
別途textから50~100文字切り出した検索用varcharカラムを用意するなど
根本的な対策が必要じゃないでしょうか?

データ容量も相当でしょうから、何らかの切り分けやグルーピングができるなら
パーティショニングなどで分散管理したほうがよいかもしれません
MySQL 5.6.6 以降はSELECT時のロックが該当するパーティションのみになるよう
効率化されたようです

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/06 13:08

    回答ありがとうございます。

    >たしかtextにはインデックスって貼れないですよね?
    ⇒INDEX作成時にカラム名に文字数を設定する事で、
     貼る事は可能(上記のCREATE TABLE文)ですが、
     なぜか効かないんですよね。。。

    そう考えると、確かに、varchar(200)等で切り出して、INDEXを貼るのは、効果的な気がします。ありがとうございます!

    キャンセル

  • 2016/09/06 14:38

    質問に追記させて頂きましたが、検索用varcharカラムでは要件を満たせませんでした。。

    > 別途textから50~100文字切り出した検索用varcharカラムを用意する「など」
    ⇒こちら、他の根本的な対策には、何が考えられますでしょうか。

    キャンセル

  • 2016/09/06 14:46

    alter table tb_test_data add index(col1,col7,url(100))
    のようなインデックスは貼ってあるという認識でよろしいですか?

    キャンセル

  • 2016/09/06 17:37

    説明不足ですみません。

    col7,col1,urlもcol1,col7,urlのINDEXも貼っていませんが、
    検索結果が
    col7での絞込件数≒実際の絞込件数であったり、
    全体の件数≒col7での絞込件数≒実際の絞込件数
    であれば、(indexにurlを含めなくても)
    Limitが効くため、上記INDEXで性能が出ることは確認していますが、
    検索結果が「1件」の場合、Limitの効果がないため、性能遅延が発生してしまいます。

    そのため、URLが1件、最初にINDEXを効かせたいのは、
    urlもしくは、col7,urlになります。

    また、説明を省いてしまいましたが、col7には、urlの一部が格納されているため、
    urlのみのINDEXで大丈夫という認識でした。
    ⇒結果、INDEXが効かないという事態に陥っています。。

    キャンセル

  • 2016/09/06 18:42

    col1は除外するのは了解しました
    場合によってはFORCE INDEXないしUSE INDEXで任意のインデックスを
    指定して検索してみるとか
    INDEX指示のある無しで効率が違うかEXPLAINで検証してみるとよいでしょう

    キャンセル

0

col7,col1,url のインデックスを作成してみると良いかもしれません。
インデックスを使えないと、100万件で45秒ほどかかる(大型コンピュータ)処理です。
ただ、すでにインデックスはたくさんあり、テーブル更新から見ると時間がかかってしまいます。
既存のインデックスはすべて必要なんでしょうか?

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/06 13:14

    ありがとうございます。

    INDEXが効けば、たとえ1億レコードあっても、1件のみの抽出に1秒かからないハズなんです。。
    そのため、col7,col1,urlのインデックスですと、
    col7で絞り込まれた100万件のスキャンがかかり、
    45秒(こちらの環境で300秒)かかってしまう状況です。

    なお、既存のINDEXは全て必要になります。
    (URLのINDEXはこのためだけなので、他に方法があれば、URLのINDEXは違う形でも構いません)

    キャンセル

0

他のかたもコメントされてますが、3項目のインデックスをいくつか試したほうがいいんじゃないでしょうか?

INDEX(url(100), col1, col7)
INDEX(col1, url, col7)
INDEX(col7, col1, url)


とか。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/06 18:31

    説明不足ですみません。
    1億レコードあるうち、ヒットするURLが1件のみの場合、
    INDEX(url(255))の効果がなく、性能劣化が発生します。
    他のINDEXについては、試しており、質問に追記しました。

    キャンセル

0

カラムを等価な式に変えるとDBエンジンは等価と判断できないことを利用して
(DBエンジンをあえてあざむいて)、
SELECT T1.col7+0 = 123456 AND T1.url LIKE ....
SELECT CONCAT(T1.cok7,’’) = 123456 AND T1.url LIKE ....
のように col7と直に比較評価しない条件指定をすると、インデックスのあるurlの方を
優先的に比較評価するかもしれません。
注)col7がNULLであると言う条件を指定できない欠陥があります。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/13 02:28

    回答ありがとうございます。
    試しましたが、やはりURLのINDEXは実行計画・実測共に効きませんでした。
    なお、col7はNOT NULL項目なので、NULLになる事はありませんので、
    ご指摘の注意事項には該当しないと思われます。

    キャンセル

0

私もTEXT型の列に128byteなどの指定をしてINDEXを作り運用しています。
直接的な解答にはならないかと思いますが、1億という数が影響している可能性があるのであれば確認のため5,000万、1,000万など数を落としてINDEXが働くか見てみるのは如何でしょうか。
INDEXのために必要なメモリや物理的な書き込みなどがボトルネックになっている可能性がないか切り分けが進められるかも知れません。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/14 12:52

    回答ありがとうございます。
    ためしに、col7で絞り込める件数(300万件)のみのテーブルを用意しましたが、
    性能劣化が発生してしまっている状況です。

    キャンセル

0

  • USE INDEX や FORCE INDEX を付加したときと、しなかったときの、実行計画の違い
  • SHOW PROFILE により、どこに時間がかかっているかの分析

があれば助けになるような気がします。

”存在しないURL”を指定すると、基本的に、INDEXが効きいて、1秒以内のレスポンスになりました。 
ただし、"存在しないURL"が50文字以上となると、INDEXが効かず、300秒くらいのレスポンスになってしまいました。。

ということですので、INDEX が効かない場合に、何に時間がとられているか…でしょう。

特定の場合にだけ劣化する場合

  • 一度、「col7 等は無視して、URL のみで該当する記事数を count で取るクエリ」若しくは、LIMIT 付きのクエリで URL に該当があるか、ないかだけを確認

し、その後発行するクエリを変えてみる手はあるかもしれません。ざっくりした言い方ですみません。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/14 12:55

    回答ありがとうございます。

    SHOW PROFILEは
    MySQL 5.6.7以降、非推奨なので、あまり信用できず。。
    色々なSQLを実行していますが、
    遅延原因はURLのINDEXが効いていないために、
    別のINDEXによる検索が走っているのが原因になります。

    もうちょっと調べてみます。

    キャンセル

  • 2016/09/14 13:11

    そのための USE INDEX なんですが
    /* https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html */

    キャンセル

  • 2016/09/14 13:27

    そうですね、特定の状況で、パフォーマンスが大幅に異なるという点で、私が以前行った質問
    https://teratail.com/questions/43797
    と似ているかなとは思ったのですが。INDEX が効かないのであれば、URL だけのクエリにしても効かないのかどうか、その際に USE INDEX をつけたら速度が変わってくるか…というのが気になるところでした。
    unknown00012 様の言うように、そのための USE INDEX なので、どこにネックがあるのか…というところですね。

    キャンセル

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

  • MySQL

    7002questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    3016questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • データベース

    837questions

    データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

  • データベース設計

    182questions

    データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

  • パフォーマンス

    25questions

    コード効率の向上や計算に関する質問には、このタグを使ってください。