Join tables is too expensive, sort the table and use
CASE-WHEN will be faster.
I try to use
CASE-WHEN but got Wrong Answer at first.
After debug for an hour, I found that the
:='s priority is lower than
It's amazing, I don't know why MySQL designed as that.
CASE-WHEN solution (1056 ms):
SELECT Id FROM ( SELECT Id, ( CASE WHEN (@prevDate + 1 = TO_DAYS(Date)) AND (@prevTemp < Temperature) THEN ((@prevTemp := Temperature) IS NOT NULL AND (@prevDate := TO_DAYS(Date)) IS NOT NULL) WHEN ((@prevTemp := Temperature) IS NOT NULL AND (@prevDate := TO_DAYS(Date)) IS NOT NULL) THEN FALSE END ) flag FROM Weather, (SELECT @prevTemp := NULL, @prevDate := NULL) a ORDER BY Date ) c WHERE flag = TRUE