Mysql の SELECT 表記について

Question

以下のコードですが、xxxテーブルからデータを抽出して表示したいのですが、⇒矢印の行を追加すると表示時間が異常に遅くなり、抽出データの量によっては timeout することがあります。表示時間を短くしtimeoutを避けるためにはどうしたらよろしいでしょうか？ご教授お願い致します。

＜追記＞
create table xxx (id int, AAA varchar(100), BBB varchar(100),CCC varchar(100),DDDvarchar(20));
AAA 　　BBB 　　 CCC 　　　DDD
=============＝＝＝＝＝＝＝
チョコ　　170円　菓子類　　　111A
チョコ　　160円　菓子類 111A
アイス　　200円　アイス類 111B

ざっくりいうと、DDDの値が同一なら、AAAの値を重複させずに表示させたいのです。
（表示結果が「チョコ、チョコ、アイス」ではなく「チョコ、アイス」にしたい）

<?php

	$pdo = new PDO("mysql:host=xxx; dbname=db; charset=utf8", "xxx", "pass", array( PDO::ATTR_EMULATE_PREPARES => false ) );
	$sql = "select * from xxx where 1 = 1 ";
	$condition = array();

	if( !empty( $_REQUEST["drug"] ) ){
	$sql = $sql . " and ( AAA like :aaa or BBB like :bbb or CCC like :ccc  ) ";　
　　⇒$sql = $sql . " and ( id IN(SELECT min(id) FROM xxx GROUP BY DDD)  ) ";
          
		$condition[":aaa"] = "%{$_REQUEST["aaa"]}%";
                $condition[":bbb"] = "%{$_REQUEST["bbb"]}%";
	       $condition[":ccc"] = "%{$_REQUEST["ccc"]}%";

	}

    $statement = $pdo->prepare( $sql );
    $statement->execute( $condition );
	$results = $statement->fetchAll();

foreach ($results as $result) {

print $result['AAA'];
}
?>

Comments

[m.ts10806]

テーブル定義とサンプルデータをSQLでご提示ください

[alkeran]

create table xxx (id int, AAA varchar(100), BBB varchar(100),CCC varchar(100),DDDvarchar(20));
AAA 　　BBB 　　 CCC 　　　DDD
=============＝＝＝＝＝＝＝
チョコ　　170円　菓子類　　　111A
チョコ　　160円　菓子類 111A
アイス　　200円　アイス類 111B

[alkeran]

ざっくりいうと、DDDの値が同一なら、AAAの値を重複させずに表示させたいのです。
（表示結果が「チョコ、チョコ」ではなく「チョコ」のみ）

[m.ts10806]

質問は編集できますので編集願います。

[alkeran]

承知しました。ありがとうございます。

[XNXSXMXR]

「⇒」のクエリで最小の id を求めていますが、この条件を入れると「160円」のデータはヒットしなくなりませんか？

[alkeran]

初心者のため的を得ていない返答なら申し訳ありません。160円のチョコは表示させたくありません。DDDのカラムでお菓子のコードが一緒ならAAAカラムの表記は1種類にしたいのです。ただ、今回は表示はできるのですが、それまでの時間がものすごくかかってしまいます。

[XNXSXMXR]

なるほど、表示されなくても良いのが織り込み済みということですね。

Answer 1

他の回答者様にもご指摘があるように、どこでどのように遅くなっているか「実行計画」(explain）を行ってみる必要があるかと思います。

SQLを直接実行できる環境（コンソールだったりphpMyAdminだったり他ツールでも）はありますか？

explain analyse
select * from xxx where 1 = 1 
 and ( AAA like '%チ%' or BBB like '%円%' or CCC like '%類%'  )
 and (id in (select min(id) from xxx group by DDD));

※上記「%チ%」や「%円%」は適当に指定しています。
実際にブラウザで入力している値を「%」と「%」の間にセットしてみてください。

これを実行し、結果を質問欄に追記してください。
何か原因が見つかるかもしれません。

思ったのが、LIKE検索は全件検索しに行くので、データ件数が多いほど時間がかかります。

Comments

[alkeran]

以下のエラーコードが表示されました。id数は62913個あります。やはりid数が多いため時間がかかるのでしょうか？like検索が重くしていると思い、⇒の行を外して実行したが素早く表示されました。もしかして、group by DDD　が原因でしょうか？

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from xxx where 1 = 1
and ( AAA like '～

[XNXSXMXR]

実際に実行したSQLをご提示ください。

[alkeran]

以下のコードです。
explain analyse
select * from xxx where 1 = 1
and ( AAA like '%チ%' )
and (id in (select min(id) from xxx group by DDD));

[XNXSXMXR]

１つスペルミスがありました。
「explain analyse」ではなく、「explain analyze」でした。すみません。

あと、こちらで同様のテーブルと8万件のレコードを作って試してみましたが、１～２秒で結果が取得できました。

質問者さまの環境と異なるので単純な比較ができないのですが、実行計画のクエリ(スペルを直して）をもう一度実行してみてください。

●マシンスペック（仮想マシン）
・CPU ... i5 2.5GHz (1コア)
・メモリ ... 1024MB
・MySQL ... Ver.8.0.18

■実行結果
mysql> explain analyze
-> select * from xxx2 where 1 = 1
-> and ( AAA like '%チ%' )
-> and (id in (select min(id) from xxx2 group by DDD));
+---------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------+
| -> Filter: ((xxx2.AAA like '%チ%') and <in_optimizer>(xxx2.id,xxx2.id in (select #2))) (cost=8069.75 rows=8867) (actual time=844.340..1566.456 rows=6 loops=1)
-> Table scan on xxx2 (cost=8069.75 rows=79815) (actual time=0.030..402.315 rows=81798 loops=1)
-> Select #2 (subquery in condition; run only once)
-> Table scan on <temporary> (actual time=0.005..0.026 rows=10 loops=1)
-> Aggregate using temporary table (actual time=844.145..844.202 rows=10 loops=1)
-> Table scan on xxx2 (cost=8069.75 rows=79815) (actual time=0.011..373.403 rows=81798 loops=1)
|
+---------------------------------------------------------------------------+
1 row in set (1.57 sec)

[alkeran]

返信が遅れまして、申し訳ございませんでした。
SELECT DISTINCT　により、時間がかからず表示することができました。
解決方法を提案していただいたXNXSXMXRさんをベストアンサーにしたいと思います。
他の皆様もありがとうございました。

Answer 2

質問に CREATE TABLE, CREATE INDEX を追記してください。
MySQL SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう も質問に追記できた方が良いです。

　⇒$sql = $sql . " and ( id IN(SELECT min(id) FROM xxx GROUP BY DDD) ) ";

DDD列とid列でインデックスが設定されていなければインデックスを追加してみては？

Comments

[alkeran]

create table xxx (id int, AAA varchar(100), BBB varchar(100),CCC varchar(100),DDDvarchar(20));
申し訳ございません。ご指摘ありがとうございます。id列にindexが追加されています。DDD カラムは計12桁の英数字（アルファベットが１文字のみ）の文字列です。

[Orlofsky]

質問に追記してください。
DDDとidの2つの列でインデックスを追加してください。

[Orlofsky]

インデックスの追加の前と後での実行計画の追記も忘れずに。

[alkeran]

DDDとidの2つの列にindexを追加しました。

[alkeran]

"Internal Server Error"というエラーが出ました。

[Orlofsky]

アドバイスをほとんど理解できていないようです。
有料で助っ人を呼んだほうが良いです。
https://geechs-job.com/client/inquiry

[alkeran]

ありがとうございます。

[m.ts10806]

そこで納得しちゃうのか…

[Orlofsky]

有償サイトや対面のセミナーで勉強した方がよろしいかと。

[Orlofsky]

各列もなるべくわかり易いネーミングに書き直したほうが良いんですけどね。
昔、NECが作ったかアドバイスしたCOBOLで項目名が連番で統一されているシステムが酷すぎて、システム部の部長の了解を取って全部作り直したことがあります。項目名のミスが原因のバグがボロボロ見つかりました。全面的に作り直して、同じハードウェア・同じデータ量で15時間かかっていたバッチが1時間で終わりました。

[Orlofsky]

コードやデータは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。