MySQL Forums
Forum List  »  General

Why setting a variable makes the select too long?
Posted by: ran cohen
Date: October 11, 2021 11:03PM

I have a select that has the number 5 show up in a few places.
I tried to define a variable for the select, but that made the select take too long.

This is the original select - it takes about 2 seconds in the workbench:
SELECT * from Backtest.cash_at_date
where cash = (
SELECT max(cash) FROM Backtest.cash_at_date
where month(date) = 5)
or cash = (SELECT max(cash) FROM Backtest.cash_at_date
where month(date) = 5 and id != (
SELECT id FROM Backtest.cash_at_date
where cash = (
SELECT max(cash) FROM Backtest.cash_at_date
where month(date) = 5)))

This is the modified select - it crashes in the workbench after 30 seconds, and right now I tried running it in ubuntu - it keeps on running after several minutes:
SET @MONTH = '5';
SELECT * from Backtest.cash_at_date
where cash = (
SELECT max(cash) FROM Backtest.cash_at_date
where month(date) = @MONTH)
or cash = (SELECT max(cash) FROM Backtest.cash_at_date
where month(date) = @MONTH and id != (
SELECT id FROM Backtest.cash_at_date
where cash = (
SELECT max(cash) FROM Backtest.cash_at_date
where month(date) = @MONTH)))

Options: ReplyQuote


Subject
Written By
Posted
Why setting a variable makes the select too long?
October 11, 2021 11:03PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.