MySQLでのNOT EXISTSを置き換えて高速化方法で試行錯誤中
数日試行錯誤しており、
解決できない為、ご質問方法も初心者で内容も初歩的でここでのご質問は失礼かとも思いましたが、
解決へのご指導いただきたく、投稿させて頂きますことをお許し頂ければと思います。
■問題点
果物での例ですが、
MySQLで、
・company_idで指定したお客様会社で、
・果物ごとに購入日buy_dateが、
・本日以降がないデータが、抽出対象
・昨日以前のデータで、且、最大日のデータを抽出
・最大日のレコードが複数該当した場合は全て抽出
のレコードを抽出したいのです。
NOT EXISTSを使用し正常に該当レコードを抽出できていたのですが、
レコード件数が、約9万件になった現在約20秒の抽出時間がかかるようになってしまいました。
そこで、試行錯誤していたのですが、
ネットで、NOT EXISTSをLEFT JOINに置き換えて高速化という開発アドバイスのページを見つけ、
SQL文を組みなおしたのですが、SQL文の文法エラーになります。
⇒ SQL実行エラー#1109.データベースメッセージ
"Unknown table 'f1' in where clause"
f1のスコープが、SQL文の中まで有効でない為にでていると考えていますが、
回避策を調べても、試行錯誤しても、解決できずに困っています。
どのような記述方法にすれば、このSQLの抽出を実現できるSQL文にできますでしょうか？
▼food テーブル構造
CREATE TABLE `food` (
`id` int(11) NOT NULL auto_increment,
`company_id` int(11) NOT NULL default '0',
`food_id` int(11) NOT NULL default '0',
`buy_date` date default NULL,
`food_name` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `company_id` (`company_id`),
KEY `food_id` (`food_id`),
KEY `buy_date` (`buy_date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
▼food テーブルデータ例
int int verchar(255) date int ・・・
AutoIndent key key key ・・・
主キー
---------------------------------------------------------------
id food_id food_name buy_date company_id・・・
---------------------------------------------------------------
1 1 みかん 2018-03-01 100 ・・・
2 1 みかん 2018-03-01 200 ・・・ ★抽出対象
3 2 りんご 2018-03-05 200 ・・・
4 3 ばなな 2018-02-23 200 ・・・ ★抽出対象
5 2 りんご 2018-03-10 200 ・・・ ★抽出対象
6 5 なし 2017-12-31 200 ・・・
7 2 りんご 2018-03-19 100 ・・・
8 4 まんごー 2018-05-30 200 ・・・ ※本日以降
9 5 なし 2018-03-20 200 ・・・ ※本日以降
： ： ： ： ： ：
---------------------------------------------------------------
上記テーブルから、
id=2,4,5を抽出したいです。
▼ネットに『NOT EXISTSが遅いのを高速化する方法』として見つけた記述方法。
01 SELECT *
02 FROM
03 food AS f1
04 LEFT JOIN (
05 SELECT
06 DISTINCT f2.food_id
07 FROM
08 food AS f2
09 WHERE
10 (f2.company_id = 200)
11 AND (f1.company_id = 200)
12 AND (f1.food_id = f2.food_id)
13 AND (f1.buy_date < f2.buy_date)
14 ) LOG_TEMP
15 ON
16 LOG_TEMP.food_id = f1.food_id
17 WHERE
18 LOG_TEMP.food_id is null;
▼現在、使用しているがレコード数が増え、著しく処理が遅くなってきたSQL文。
・テーブル内全レコード数：約9万件
・抽出されるレコード数：812件
・処理時間：約18～22秒
SELECT *
FROM
food AS f1
WHERE
NOT EXISTS (
SELECT 1
FROM
food AS f2
WHERE
(f2.company_id = 200)
AND (f1.company_id = 200)
AND (f1.food_id = f2.food_id)
AND (f1.buy_date < f2.buy_date)
)
以上です。
▼本件の結果のご報告 (2018/03/22 以下追記)
問題解決した最終SQL文。
・処理速度：約20秒 → 約0.15秒
・確認時の抽出レコード：781件（件数は運用中サーバーの為、刻々と変化）
・抽出レコード内容：改善前と同一内容・件数
・結果：改善成功！！
・最終のSQL文
SELECT f2.*
FROM
(
SELECT food_id, MAX(buy_date) AS max_buy_date
FROM
food
WHERE
company_id = 200
GROUP BY food_id
) AS f1
INNER JOIN food AS f2
ON
(f1.food_id = f2.food_id)
AND (f1.max_buy_date = f2.buy_date)
WHERE
(f2.company_id = 200)
ごたごたのご質問でしたが、
適切なご指摘やSQL文のご提示をありがとうございました。
大変お手数やお心遣いありがとうございました。