MySQL query exponentially slower with parameter in subquery vs hard-coded value I have the following MySQL query that is querying naughty ip addresses from the last 24 hours and ban count >= 2. I don't want them if they are in an extra naughty ban list from the last 7 days and ban count >= 5. The query takes 3 minutes to run. If I replace @__cutOff_1 with the actual hard coded date inline in the query instead of a variable, the query takes 2 seconds to run.

Is this a bug in MySQL? The query explainer looks like this:



I am using the latest MySQL x64 8.0 on Windows 10 x64 as of 2019-07-30. Submissions table has 8 million rows, ip addresses table 360K. All where clauses are indexed.

SET @__cutOff_0 = '2019-07-29';
SET @__minBanCount_1 = 2;
SET @__cutOff_1 = '2019-07-23';

SELECT `i`.`IPAddress`, `i`.`BanCount`, MAX(`s`.`Timestamp`) AS `c`
FROM `Submissions` AS `s`
CROSS JOIN `IPAddresses` AS `i`
WHERE (((`s`.`Timestamp` &gt;= @__cutOff_0) AND (`i`.`BanCount` &gt;= @__minBanCount_1)) AND (`s`.`IPAddressId` = `i`.`IPAddress`)) AND `i`.`IPAddress` NOT IN (
    SELECT DISTINCT `i2`.`IPAddress`
    FROM `Submissions` AS `s2`
    INNER JOIN `IPAddresses` AS `i2` ON `s2`.`IPAddressId` = `i2`.`IPAddress`
    WHERE (`s2`.`Timestamp` &gt;= @__cutOff_1) AND (`i2`.`BanCount` &gt;= 5)
)
GROUP BY `i`.`IPAddress`, `i`.`BanCount`
ORDER BY `c` DESC
Posted a bug / feature request with Oracle, they say it is a known issue. So there are two work-arounds:

1] Only use hard-coded values in sub-queries, no parameters. Not really an option for me, but may be for some people.

2] Create a temporary data set and filter the outer query with it in code behind. This is the route I went with. I create a set of strings for the inner query using a separate query, then I filter the outer query with this set in code behind to produce the final set.