Replacing NOT EXISTS in MySQL, trial and error using acceleration method

Asked 2 months ago, Updated 2 months ago, 3 views

After several days of trial and error,
As I can't solve this problem, I thought it might be rude to ask questions here because I'm a beginner and the content is rudimentary.
I would appreciate it if you could allow me to post a post to help you solve this problem.

■Problems
This is an example of a fruit.
In MySQL, click
·At the customer company specified by company_id,
·The buy_date for each fruit is
·Data that is not available after today will be extracted
·Extract data from data prior to yesterday and for the maximum date
·If more than one record of the maximum date is applicable, all records are extracted
I'd like to extract records for the .

I was able to successfully extract the record using NOT EXISTS.
Now that the number of records is approximately 90,000, it takes about 20 seconds to extract them.

So I tried and tried, but
On the Internet, I found a page of development advice about replacing NOT EXISTS with LEFT JOIN and accelerating it.
I reorganized the SQL statement, but I got a grammatical error in the SQL statement.

SQL SQL Execution Error #1109.Database Message
"Unknown table 'f1' in where cause"

I believe that the scope of f1 is not valid even in SQL statements, but
Even if I look into the workaround, I can't solve it through trial and error, so I'm in trouble.
How can I use this SQL extraction method to create a SQL statement?

▼food table structure

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 table data example

 int int verchar(255) date int...
AutoIndent key key...
primary key   
---------------------------------------------------------------
 id food_id food_name buy_datecompany_id...
---------------------------------------------------------------
  11 Tangerine 2018-03-01 100...
  21 Tangerine 2018-03-01 200... ★ To be extracted
  32 Apple 2018-03-05 200...
  43 Banana 2018-02-23 200... ★ To be extracted
  52 Apple 2018-03-10 200... ★ To be extracted
  65 None 2017-12-31200...
  72 Apple 2018-03-19100...
  84 Mango 2018-05-30200... *After today
  No 952018-03-20200... 本日After today
  :       :        :             :        :         :
---------------------------------------------------------------

From the table above,
I would like to extract id=2,4,5.

▼ A method of writing found on the Internet as a way to speed up NOT EXISTS.

01 SELECT*
02 FROM
03 food ASf1
04 LEFT JOIN(
05 SELECT 
06 DISTINCT f2.food_id
07 FROM
08 food ASf2
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 statements that are currently in use but are significantly slower due to increased records.

·Total number of records in the table: approximately 90,000
·Number of records extracted: 812
·Processing time: approximately 18-22 seconds

SELECT*
  from
    food ASf1
  WHERE
    NOT EXISTS (
      SELECT1
      from
        food ASf2
      WHERE
            (f2.company_id=200)
        AND (f1.company_id=200)
        AND(f1.food_id=f2.food_id)
        AND (f1.buy_date<f2.buy_date)
    )

That's all.

▼Report of the results of this case (add below March 22, 2018)

Final SQL statement resolved.
·Processing speed: approximately 20 seconds → approximately 0.15 seconds
·Extract records at the time of confirmation: 781 cases (the number of cases changes every minute because it is a server in operation)
·Contents of extracted records: Same contents as before improvement ·Number of cases
·Results: Successful improvement!!
·Last SQL statement

SELECT f2.*
from
  (
  SELECT food_id,MAX(buy_date)AS max_buy_date
  from
    food
  WHERE
    company_id = 200
  GROUP BY food_id
  ASf1
INNER JOIN FOOD ASf2
ON
      (f1.food_id=f2.food_id)
  AND (f1.max_buy_date=f2.buy_date)
WHERE
  (f2.company_id=200)

Sorry for the confusion, but
Thank you for pointing out the appropriate SQL statement.
Thank you for your time and consideration.

mysql sql

2022-09-30 13:45

1 Answers

If you want to extract records for maximum days, we recommend that you use the aggregation function MAX instead of using NOT EXISTS or LEFT OUTER JOIN."I heard that ""data from yesterday or earlier"", but the definition of ""strong>yesterday" was ambiguous, so I compared it to ""code>CURDATE() for now."Watch out for time zones in MySQL operating environments.

SELECT food_id,MAX(buy_date)
FROM food ASf1
WHERE company_id = 200 AND buy_date<CURDATE()
GROUP BY food_id

This gives you the maximum buy_date per food_id.Self-coupling this

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)ASf1
INNER JOIN FOOD ASf2
ON f1.food_id=f2.food_id AND f1.max_buy_date=f2.buy_date
WHERE f2.company_id = 200

You can find the appropriate line.There was no mention of multiple records for the maximum date, so I try to list them all for now.

Data that is not available after today is extracted

This is where NOT EXISTS and LEFT OUTER JOIN appear.But

Extract data from yesterday and up to date

In conjunction with , you can simply say, "Exclude if the maximum date is today or later."

SELECT f2.*
FROM(SELECT food_id,MAX(buy_date)AS max_buy_date
      FROM food
      WHERE company_id = 200
      GROUP BY food_id)ASf1
INNER JOIN FOOD ASf2
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

Is that so?


2022-09-30 13:45

If you have any answers or tips


© 2022 OneMinuteCode. All rights reserved.