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

###データーベースの検索表示の速度を速くするには？
下記のように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段階で検索表示させています。

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

製品名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

yambejp

2016/08/10 09:11

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

行動規範の内容に同意します

回答4件

ベストアンサー

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

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

考えられる原因は次の2つですが、

適切なindexが無いから
detail.phpの表示データ量が多すぎる

勘ですが、常にではなく遅いケースと早いケースがあるところから大きく影響してるのは｢2｣ではないかと思います。試しにdetail.phpのSQLをこう代えると、常に軽くなるんじゃないでしょうか？

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

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

投稿2016/08/10 11:19

hirohiro

総合スコア2068

hirohiro

2016/08/10 11:45 編集

DB使うたびに思うんですがなかなか恐ろしい性能で、登録データ件数が25万件程度だと、相関サブクエリもない少量抽出のSQLならindexがなくても結構短時間で処理終わらせてしまったりするんですよね。(人間にとって一瞬なだけですけどね) だから良くない構成でも大量アクセスが来るまで問題に気がつかなかったりします。 (なのでもし私の記述が当たりでも、index設計は考慮したほうがいいです) でも抽出データが巨大な場合はそれだけで速度が如実に落ちます。 DBサーバからPHP(Webサーバ)へデータ転送、PHP内で一行ずつ処理(これが遅い)、出来たHTMLをクライアントに送信(これもそこそこ遅い)、クライアントのブラウザが必死にDOMツリーを作成(これもなかなか遅い)、ページ先頭から徐々に表示、とデータが多いと複数の工程全部で遅くなるので、合計すると結構な時間になったりして、テスト環境でもはっきり分かる事が多いです。

iamsin

2016/08/10 13:48

ご返答誠にありがとうございます。また、ご返事いただいた方々に1点、訂正とお詫び申し上げます。今まで、テスト試験として、少量のデータ数をアップして、IE,Crome,FireFox,iPhoneで検索テストをしてきました。その際は問題なく稼働していました。データー数を大幅にアップしてから検索速度が遅いことに気付きました。データー増加後はiPhoneのみのテスト検索しかしておらず、PCでの検索をしていなかったのです。検索したところ、PCでの検索テストは、１）、２）の検索表示はスムーズに表示されました。これはiPhone、ブラウザ側に問題があるということでしょうか？もし、お気づきの点とうございましたら、ご指導いただけると助かります。何度も申し訳ございません。（ちなみに、皆様のご指摘のインデックス設定を色々と検討しましたが、変化はありませんでした。LIKE検索の場合は、インデックスは有効ではないようです。）

hirohiro

2016/08/10 14:16 編集

定型コメントのようですので未対応かも知れませんが、1)で3000件表示した場合で、2)にdetail.phpには10件くらいしか表示しない商品の詳細をクリックした場合も遅いですか？ >　これはiPhone、ブラウザ側に問題があるということでしょうか？これは原因になりうるものが多すぎて一概には言えません。・たまたまそのときネットワークが重かったのかも知れませんし・そのiPhoneのスペックが低いからかも知れませんし・複数アプリが起動していてiPhoneブラウザの実行メモリが圧迫されていたのかもしれません。・iPhoneのOSツールやバージョンアップが裏で走っていたのかも知れません。・たまたまテストサーバ側が何らかの処理でビジーだったケースも想定できます

行動規範の内容に同意します

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

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

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

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

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

原則前方一致のみの検索に制限する(前方一致はインデックスが利用されます)
前方一致と後方一致それぞれを設け、後方一致の場合は関数インデックスを利用する
前後曖昧検索が譲れない場合はLIMITなどで抽出件数を絞りページングを採用とする

曖昧検索を排除すると、
利便性が悪くなるのはもちろんなのて一般的にはPG難易度は上がりますがページングを採用するケースが多いと思われます。

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

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

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

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

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

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

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

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

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

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

ご参考までに・・・

投稿2016/08/10 10:01

総合スコア1636

2016/08/10 13:51

2016/08/10 14:52

コメント確認しました。正直なところ今の段階で原因を特定するには材料が少ないですね。 hirohiroさんがコメントで掲示してますが色々な原因が考えられます。地道にはなりますが1つずつ原因の切り分けを行うしかないでしょう。例えばiPhoneとPCを並行して動作を確認し、 PCが一瞬で帰ってきて、iPhoneだけが遅い場合はサーバ側の要因という部分は排除できます。後はスマートフォンでも複数端末で動作を検証したり、ブラウザを変更して動作を試してみたりと1つずつ試して可能性を排除するしかないですね。ちなみに動作検証する際はまずスマートフォン側も、バックグラウンドで動作しているアプリなど余計なものを全て取り除いた状態から検証を開始するのが望ましいでしょう。

行動規範の内容に同意します

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

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