

数日試行錯誤しており、  
解決できない為、ご質問方法も初心者で内容も初歩的でここでのご質問は失礼かとも思いましたが、  
解決へのご指導いただきたく、投稿させて頂きますことをお許し頂ければと思います。

■問題点  
果物での例ですが、  
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 テーブル構造

[code]

    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;
    
[/code]

▼food テーブルデータ例

[code]

    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       ・・・   ※本日以降
      ：       ：        ：             ：        ：         ：
    ---------------------------------------------------------------
    
[/code]

上記テーブルから、  
id=2,4,5を抽出したいです。

▼ネットに『NOT EXISTSが遅いのを高速化する方法』として見つけた記述方法。

[code]

    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;
    
[/code]

▼現在、使用しているがレコード数が増え、著しく処理が遅くなってきたSQL文。

・テーブル内全レコード数：約9万件  
・抽出されるレコード数：812件  
・処理時間：約18～22秒

[code]

      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)
        )
    
[/code]

以上です。

* * *

▼本件の結果のご報告 (2018/03/22 以下追記)

問題解決した最終SQL文。  
・処理速度：約20秒 → 約0.15秒  
・確認時の抽出レコード：781件（件数は運用中サーバーの為、刻々と変化）  
・抽出レコード内容：改善前と同一内容・件数  
・結果：改善成功！！  
・最終のSQL文

[code]

    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)
    
[/code]

ごたごたのご質問でしたが、  
適切なご指摘やSQL文のご提示をありがとうございました。  
大変お手数やお心遣いありがとうございました。


CC BY-SA 3.0
2018-03-19T10:08:51.920
42492
2018-03-22T07:36:34.150
2018-03-22T07:36:34.150
27805
27805
question
mysql sql
MySQLでのNOT EXISTSを置き換えて高速化方法で試行錯誤中


「最大日のレコードを抽出したい」のであれば`NOT EXISTS`や`LEFT OUTER
JOIN`を使うのではなく素直に集計関数`MAX`を使用することをお勧めします。「昨日以前のデータ」とのことでしたが **昨日**
の定義があいまいなのでとりあえず`CURDATE()`との比較にしておきました。MySQLの動作環境でのタイムゾーンにお気を付けください。

[code]

    SELECT food_id, MAX(buy_date)
    FROM food AS f1
    WHERE company_id=200 AND buy_date < CURDATE()
    GROUP BY food_id
    
[/code]

これで`food_id`ごとの最大の`buy_date`が得られます。これを自己結合すれば

[code]

    SELECT f2.*
    FROM (SELECT food_id, MAX(buy_date) AS max_buy_date
          FROM food
          WHERE company_id=200 AND buy_date < CURDATE()
          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
    
[/code]

該当行が得られます。最大日のレコードが複数該当した場合について言及されていなかったので、ひとまず全て列挙されるようにしています。

* * *

> 本日以降がないデータが、抽出対象

これこそ`NOT EXISTS`や`LEFT OUTER JOIN`の出番ですね。とはいえ

> 昨日以前のデータで、且、最大日のデータを抽出

と組み合わせると、「最大日が本日以降となる場合は除外する」と簡単に表せます。

[code]

    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 f1.max_buy_date < CURDATE() AND f2.company_id = 200
    
[/code]

でしょうか。


CC BY-SA 3.0
2018-03-20T01:42:29.890
42505
2018-03-20T08:11:33.867
2018-03-20T08:11:33.867
4236
4236
42492
answer