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.
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
▼ Food table data example
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.
▼ 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
▼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
Sorry for the confusion, but
Thank you for pointing out the appropriate SQL statement.
Thank you for your time and consideration.
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.
This gives you the maximum
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."
Is that so?
© 2022 OneMinuteCode. All rights reserved.