1

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:

enter image description here

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` >= @__cutOff_0) AND (`i`.`BanCount` >= @__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` >= @__cutOff_1) AND (`i2`.`BanCount` >= 5)
)
GROUP BY `i`.`IPAddress`, `i`.`BanCount`
ORDER BY `c` DESC
5
  • Have tried the same query in MS SQL Server and even sqlite, it runs in just a few seconds.
    – jjxtra
    Commented Jul 31, 2019 at 15:05
  • 1
    I think you have answered yourself, then until it is fixed, consider using prepared statements to workaround the issue, rather than MySQL variables.
    – jynus
    Commented Jul 31, 2019 at 15:59
  • This was done using a prepared statement using a C# MySqlCommand and calling Prepare function.
    – jjxtra
    Commented Jul 31, 2019 at 16:02
  • " All where clauses are indexed." -- Please provide SHOW CREATE TABLE.
    – Rick James
    Commented Aug 1, 2019 at 0:07
  • Please post results of A) SHOW INDEX FROM submissions; and SHOW INDEX FROM ipaddresses; for analysis. Using a version of MySQL with less than 90 days on the street as GA could mean you have to report bugs no one has found in their effort to be on the leading edge of using the newer version. What version are you using? Commented Aug 1, 2019 at 5:01

2 Answers 2

1

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.

0

For clarity, change

    CROSS JOIN  `IPAddresses` AS `i`
    WHERE  (`s`.`IPAddressId` = `i`.`IPAddress`)

To

    JOIN  `IPAddresses` AS `i`  ON  (`s`.`IPAddressId` = `i`.`IPAddress`)

Change

      AND  `i`.`IPAddress` NOT IN (
        SELECT  DISTINCT `i2`.`IPAddress`
                   ... )

to

     AND NOT EXISTS ( SELECT 1 FROM  AS `IPAddresses` AS `i2` ...
                          WHERE ... AND `i2`.`IPAddress` = `i`.`IPAddress` )

And have composite index:

Submissions:  INDEX(IPAddressId, Timestamp)  -- in this order

Please provide SHOW CREATE TABLE and EXPLAIN FORMAT=JSON SELECT ....

If none of my suggestions help, then jynus's workaround (prepared statements) may be best.

1
  • Thanks so much for sharing. I had already tried all of this, no change. Only making the date a hard-coded value instead of a parameter fixes things.
    – jjxtra
    Commented Aug 1, 2019 at 3:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.