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

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

ただいまの
回答率

89.04%

データーベースの検索表示の速度を速くするには?

解決済

回答 4

投稿

  • 評価
  • クリップ 0
  • VIEW 3,127

iamsin

score 15

データーベースの検索表示の速度を速くするには?

下記のようにMySQLで作成したデーターベースから、HTMLフォームに製品名を入力、
プルダウンメニューで期間を選択してデータ検索するプログラムを作成しています。

テーブル名:product
フィールド①:id        id番号    int     (主キー)
フィールド②:name   製品名     varchar (インデックス)
フィールド③:year      製造年月   int
フィールド④:serial    シリアル   varchar
フィールド⑤:area 地域        varchar
フィールド⑥:price     価格       int
フィールド⑦:note      特記       varchar

全データー数(①のid)は約250.000
製品名数(②のname) は約 50.000
ディスク使用量合計 37,968  KiB 

下記のように2段階で検索表示させています。

1) 期間を選択し、検索(あいまい検索)で一致した製品名リストを表で表示させる。

製品名A 「詳細」ボタン
製品名B 「詳細」ボタン
製品名C 「詳細」ボタン



2) 各製品名リスト毎にに設置した「詳細」ボタンをクリックし各製品の詳細情報を表示させる。同名の製品名の詳細が複数表示されます。

製品名A  製造年月  シリアル  地域  価格  特記
製品名A  製造年月  シリアル  地域  価格  特記



問題は、1)の検索結果数が多いと、2)の検索結果表示が遅くなります。

1)の検索結果数が500くらいだと、2)の検索結果も比較的スムーズに表示されますが、

1)の検索結果数が3000以上になると、2)の検索結果表示は30秒ほどもかかります。

1)の検索を、前方一致や後方一致で絞込んでを数を減らすとスムーズに表示されます。

検索の対象となるフィールドが増えると遅くなるのでしょうか?

どこが問題なのか分からず質問させていただきました。
ご指導のほどよろしくお願いいたします。

プログラムは下記の通りです。
index.php
list.php
detail.php

index.php

<BODY>
                 ~略~

<FORM method="post" action="list.php">

<SELECT name='YY'><OPTION value='2005'>2005年</OPTION></SELECT>  //2005~2016年まで選択可能
                       ~略~
<SELECT name='MM'><OPTION value='01'>  01月</OPTION></SELECT>  //  01~12月まで選択可能
                 ~略~
<SELECT name='Yy'><OPTION value='2006'>2006年</OPTION></SELECT>  //2006~2016年まで選択可能
                 ~略~
<SELECT name='Mm'><OPTION value='01'>    01月</OPTION></SELECT>   //  01~12月まで選択可能
                 ~略~

<INPUT type="text" name="kensaku" >
<INPUT type="submit" value="検索" >
</FORM>

</BODY>
</HTML>

list.php

<?php
session_start();
?>

<HTML>
<BODY>
<?php

require_once("MYDB.php");
$pdo=db_connect();

$YY  = "{$_POST['YY']}";
$MM  = "{$_POST['MM']}";
$Date  = $YY . $MM;
$Yy  = "{$_POST['Yy']}";
$Mm  = "{$_POST['Mm']}";
$date  = $Yy . $Mm;
$kensaku = "%{$_POST['kensaku']}%";

try{

$sql  = "SELECT DISTINCT `name` FROM `product` WHERE `name` LIKE '$kensaku' AND `year`>='$Date' AND `year`<='$date' ORDER BY `name` ASC ";

$stmh = $pdo->prepare($sql);

$stmh ->bindValue(':kensaku',$kensaku,PDO::PARAM_STR);
$stmh ->bindValue(':YY',$YY,PDO::PARAM_INT);
$stmh ->bindValue(':MM',$MM,PDO::PARAM_INT);

$stmh ->bindValue(':Yy',$Yy,PDO::PARAM_INT);
$stmh ->bindValue(':Mm',$Mm,PDO::PARAM_INT);

$stmh ->execute();
$count = $stmh->rowCount();

print "検索結果は".$count."件です" ;


}catch(PDOException $Exception){

print"error:".$Exception->getMessage();

}

if($count <1){

print"検索結果はありません";

}else{

?>

<TABLE>
<TBODY>

<?php
while($row = $stmh ->fetch(PDO::FETCH_ASSOC)){
?>

<TR>
<TD>
<?=htmlspecialchars($row['name'])?>
</TD>
<TD>
<FORM method = "post" action="detail.php" >
<INPUT type ="hidden" name="YYY" value="<?=$Date?>">
<INPUT type ="hidden" name="Yyy" value="<?=$date?>">
<INPUT type ="hidden" name="product_name" value="<?=$row['name']?>">
<INPUT type ="submit" value="詳細">
</FORM>
</TD>
</TR>

<?php
}
?>

</TBODY>
</TABLE>

<?php
}
?>

</BODY>
</HTML>

detail.php

<?php
session_start();
?>

<HTML>
<BODY>

<?php

require_once("MYDB.php");
$pdo=db_connect();

$YYY  = "{$_POST['YYY']}";
$Yyy  = "{$_POST['Yyy']}";
$product_name = "{$_POST['product_name']}";

try{

$sql   =  "SELECT `year`,`name`,`serial`,`area`,`price`,`note` 
          FROM `product` WHERE `name` = '$product_name' AND `year`>='$YYY' AND `year`<='$Yyy' ORDER BY `year` DESC ";

$sth   = $pdo->prepare($sql);
$sth ->bindValue(':product_name',$product_name,PDO::PARAM_STR);
$sth ->bindValue(':YYY', $YYY,PDO::PARAM_INT);
$sth ->bindValue(':Yyy', $Yyy,PDO::PARAM_INT);

$sth   ->execute();
$count = $sth->rowCount();

}catch(PDOException $Exception){

print'error:'.$Exception->getMessage();

}


if($count <1){

print'検索結果はありません<BR>';

}else{

?>

<TABLE>

<TBODY>

<?php
while($sin = $sth ->fetch(PDO::FETCH_ASSOC)){
?>

<TR>
<TD><?=htmlspecialchars($sin['year']) ?>年</TD>
<TD><?=htmlspecialchars($sin['name'])?></TD>
<TD><?=htmlspecialchars($sin['serial'])?></TD>
<TD><?=htmlspecialchars($sin['area'])?></TD>
<TD><?=htmlspecialchars($sin['price'])?>円</TD>
<TD><?=htmlspecialchars($sin['note'])?></TD>
</TR>

<?php
}
?>

</TBODY>
</TABLE>

<?php
}
?>

</BODY>
</HTML>

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

MySQL5.5
PHP5.6.22

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • yambejp

    2016/08/10 18:11

    テーブルのインデック次第じゃないですかねぇ

    キャンセル

回答 4

checkベストアンサー

0

問題は、1)の検索結果数が多いと、2)の検索結果表示が遅くなります。

list.phpへの表示商品が多いと、detail.phpの応答が遅いということですよね?
これはたまたまで、この現象は常には再現しないと思います。
もしこれが本当に原因なら、ブラウザがメモリスワップ起こして落ちかけているのではないかと
(list.phpの表示件数にかかわり無く、製品別にdetail.phpが重くなったり軽くなったりしているのでは?)

考えられる原因は次の2つですが、
1. 適切なindexが無いから
2. detail.phpの表示データ量が多すぎる
勘ですが、常にではなく遅いケースと早いケースがあるところから大きく影響してるのは「2」ではないかと思います。試しにdetail.phpのSQLをこう代えると、常に軽くなるんじゃないでしょうか?

SELECT `year`,`name`,`serial`,`area`,`price`,`note` 
FROM `product` WHERE `name` = '$product_name' AND `year`>='$YYY' AND `year`<='$Yyy' 
ORDER BY `year` DESC 
LIMIT 20"

テーブルは商品に対する何かの履歴ですよね?(入庫出庫か売り上げか)
品によってはSQLの抽出結果が数千件になってるのでは?
noteの要素は名前からしてデータ量が多そうですし。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/10 20:35 編集

    DB使うたびに思うんですがなかなか恐ろしい性能で、登録データ件数が25万件程度だと、相関サブクエリもない少量抽出のSQLならindexがなくても結構短時間で処理終わらせてしまったりするんですよね。(人間にとって一瞬なだけですけどね)
    だから良くない構成でも大量アクセスが来るまで問題に気がつかなかったりします。
    (なのでもし私の記述が当たりでも、index設計は考慮したほうがいいです)

    でも抽出データが巨大な場合はそれだけで速度が如実に落ちます。
    DBサーバからPHP(Webサーバ)へデータ転送、PHP内で一行ずつ処理(これが遅い)、出来たHTMLをクライアントに送信(これもそこそこ遅い)、クライアントのブラウザが必死にDOMツリーを作成(これもなかなか遅い)、ページ先頭から徐々に表示、とデータが多いと複数の工程全部で遅くなるので、合計すると結構な時間になったりして、テスト環境でもはっきり分かる事が多いです。

    キャンセル

  • 2016/08/10 22:48

    ご返答誠にありがとうございます。
    また、ご返事いただいた方々に1点、訂正とお詫び申し上げます。

    今まで、テスト試験として、少量のデータ数をアップして、IE,Crome,FireFox,iPhoneで検索テストをしてきました。その際は問題なく稼働していました。データー数を大幅にアップしてから検索速度が遅いことに気付きました。データー増加後はiPhoneのみのテスト検索しかしておらず、PCでの検索をしていなかったのです。検索したところ、PCでの検索テストは、1)、2)の検索表示はスムーズに表示されました。
    これはiPhone、ブラウザ側に問題があるということでしょうか?
    もし、お気づきの点とうございましたら、ご指導いただけると助かります。
    何度も申し訳ございません。

    (ちなみに、皆様のご指摘のインデックス設定を色々と検討しましたが、変化はありませんでした。LIKE検索の場合は、インデックスは有効ではないようです。)

    キャンセル

  • 2016/08/10 23:14 編集

    定型コメントのようですので未対応かも知れませんが、1)で3000件表示した場合で、2)にdetail.phpには10件くらいしか表示しない商品の詳細をクリックした場合も遅いですか?

    > これはiPhone、ブラウザ側に問題があるということでしょうか?
    これは原因になりうるものが多すぎて一概には言えません。
    ・たまたまそのときネットワークが重かったのかも知れませんし
    ・そのiPhoneのスペックが低いからかも知れませんし
    ・複数アプリが起動していてiPhoneブラウザの実行メモリが圧迫されていたのかもしれません。
    ・iPhoneのOSツールやバージョンアップが裏で走っていたのかも知れません。
    ・たまたまテストサーバ側が何らかの処理でビジーだったケースも想定できます

    キャンセル

0

nameyearにインデックスを貼っても遅いですか?

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

恐らく、productテーブルのnameカラムにインデックスが張られていないことが原因です。

以下のSQL文を実行するなどして、productテーブルの定義を確認してみてください。

SHOW CREATE TABLE product;

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

あいまい検索というのがポイントな気がしますね。

念のために実行計画は確認して欲しいのですが、
一般的に前方後方共に曖昧とするLIKE検索ではインデックスが効きません

更に言うとキーワードを入力しない場合もWHERE条件にLIKE句が付加されているので、
キーワードなしのパターンでもテーブルフルスキャンになりかねません。

キーワードなしの場合については、
もしかしたらDBMS側で最適化されるかもしれませんが、
念のため検索条件の名前が省略された際は検索条件に加えないほうがいいでしょう。

さて本題ですが、パフォーマンス悪化の回避策ですが、
一般的には以下のような案があります。

  • 原則前方一致のみの検索に制限する(前方一致はインデックスが利用されます)
  • 前方一致と後方一致それぞれを設け、後方一致の場合は関数インデックスを利用する
  • 前後曖昧検索が譲れない場合はLIMITなどで抽出件数を絞りページングを採用とする
    曖昧検索を排除すると、
    利便性が悪くなるのはもちろんなのて一般的にはPG難易度は上がりますがページングを採用するケースが多いと思われます。

追記1

ごめんなさい、一覧画面ではなく詳細画面の方が遅いのですね・・・
上記の記載は戯言なので読み飛ばしてください。
(ながら作業は良くないですね・・・)

ただ1点気になったのが、
検索結果が表示されるまでが遅いというのは本当にSQL側のレスポンスが悪いのでしょうか?

試しに、詳細画面を開く際に発行されるであろうSQLを直接MySQLに投げても30秒とかかりますか?
もし直接実行して即検索結果が返ってくるのであれば、
それはDB側ではなくWeb側の構成の問題となります。

Web側でどの程度時間がかかるか詳しく知りたい場合でしたら、
IE等のブラウザの開発者ツールというもので送受信などでかかった計測時間を調べることができます。

もしかしたらPOSTする際に送受信するデータ容量が大きすぎて、
画面遷移までに時間がかかっているだけというお話かもしれませんね。

追記2

更に余談を・・・
詳細画面表示時に発行するクエリですが、
WHERE句での条件指定、かつORDER BY句でも「year」を指定されてますね。

ちなみにORDER BY year DESCを外したら検索結果が早くなるとかはありませんか?
もしそうであればこの場合の最適解はyearカラムにインデックスを張ることです。

ORDER BYでソートを行う処理は一時領域を使って処理を行ったりと、
予想以上に負荷の高い処理だったりします(一時領域があふれると悲惨)。

ですがORDER BY句に指定するカラムにインデックスを張ってあると、
インデックスとして予めソートされたものを利用祖するため処理が高速となります。
(ソート順DESC指定のインデックスも作れたような記憶がある・・・)

また、WHERE条件の方でもインデックスレンジスキャン(インデックス範囲検索)が利用されるので一石二鳥となります。

ご参考までに・・・

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/10 22:51

    ご返答誠にありがとうございます。
    また、ご返事いただいた方々に1点、訂正とお詫び申し上げます。

    今まで、テスト試験として、少量のデータ数をアップして、IE,Crome,FireFox,iPhoneで検索テストをしてきました。その際は問題なく稼働していました。データー数を大幅にアップしてから検索速度が遅いことに気付きました。データー増加後はiPhoneのみのテスト検索しかしておらず、PCでの検索をしていなかったのです。検索したところ、PCでの検索テストは、1)、2)の検索表示はスムーズに表示されました。
    これはiPhone、ブラウザ側に問題があるということでしょうか?
    もし、お気づきの点とうございましたら、ご指導いただけると助かります。
    何度も申し訳ございません。

    (ちなみに、皆様のご指摘のインデックス設定を色々と検討しましたが、変化はありませんでした。LIKE検索の場合は、インデックスは有効ではないようです。)

    キャンセル

  • 2016/08/10 23:52

    コメント確認しました。

    正直なところ今の段階で原因を特定するには材料が少ないですね。
    hirohiroさんがコメントで掲示してますが色々な原因が考えられます。

    地道にはなりますが1つずつ原因の切り分けを行うしかないでしょう。
    例えばiPhoneとPCを並行して動作を確認し、
    PCが一瞬で帰ってきて、iPhoneだけが遅い場合はサーバ側の要因という部分は排除できます。

    後はスマートフォンでも複数端末で動作を検証したり、
    ブラウザを変更して動作を試してみたりと1つずつ試して可能性を排除するしかないですね。
    ちなみに動作検証する際はまずスマートフォン側も、
    バックグラウンドで動作しているアプリなど余計なものを全て取り除いた状態から検証を開始するのが望ましいでしょう。

    キャンセル

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

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

関連した質問

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