MySQL Query returning invalid rows and very slow

Question

I am writing an query to select player bans from another table but firstly its very slow taking around 7-14 seconds and secondly its returning invalid rows.

The first query is as follows:

SELECT *
FROM   sourcebans.sb_bans  
WHERE  removetype IS NULL
       AND removedon IS NULL
       AND reason NOT LIKE '%[FragSleuth] Duplicate account%'
       AND ip IN(SELECT DISTINCT ip
                 FROM   fragsleuth.history
                 WHERE  trackingid = "ad000c3803b48190aabf382e01b957c9")
       OR authid IN(SELECT DISTINCT steamid
                    FROM   fragsleuth.history
                    WHERE  trackingid = "ad000c3803b48190aabf382e01b957c9") 

The second query is as follows

SELECT * FROM `history` WHERE trackingid = "ad000c3803b48190aabf382e01b957c9"

And a couple of screenshots to show what I mean: First Query Second Query

In screenshot 1 you can see that its returning a row where the removedon and removetype is not null when I asked the query to only return rows with NULL.

I am also afraid that inside the history table there will be duplicate entries for the steamid and ip columns which might make the query slow, is there any way to make the query only select rows with a unique ip or steamid based on the trackingid?

Any help would be much appreciated.

Thanks

Edit: I am overwhelmed by the help, Thanks to @maraca, @Skorpioh and @Adam Silenko, the query time is now less than a second!

Answer 1

the and have higher priority then or... You need have to index on your tables np. add index to trackingid field in fragsleuth.history if you don't have

You can probably do faster using one sub query, but i'm not sure this.

SELECT *
FROM   sourcebans.sb_bans  
WHERE  removetype IS NULL
AND removedon IS NULL
AND reason NOT LIKE '%[FragSleuth] Duplicate account%'
AND exists (
  SELECT 1 from fragsleuth.history
  WHERE  trackingid = "ad000c3803b48190aabf382e01b957c9"
  and (ip = sourcebans.ip or steamid = sourcebans.authid) )

Answer 2

The query returns rows that are not NULL because it is interpreted as
(... AND ... AND ... ) OR ... instead of
... AND ... AND ( ... OR ...)

So you need to add a braces, also the DISTINCT is not needed:

SELECT *
FROM   sourcebans.sb_bans  
WHERE  removetype IS NULL
       AND removedon IS NULL
       AND reason NOT LIKE '%[FragSleuth] Duplicate account%'
       AND (ip IN(SELECT ip
                 FROM   fragsleuth.history
                 WHERE  trackingid = "ad000c3803b48190aabf382e01b957c9")
       OR authid IN(SELECT steamid
                    FROM   fragsleuth.history
                    WHERE  trackingid = "ad000c3803b48190aabf382e01b957c9"))

Answer 3

You have an operatior precendence issue here and that's why it ends up having results where removetype/removedon isn't null.

If you check http://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html you'll see that AND is higher priority than OR meaning your query will run all the predicates glued together with the "AND" operator and only afterwards do the OR meaning you will see results where the authorid is a match and the rest doesn't matter anymore.

If I'm not wrong the below should work correctly:

SELECT *
FROM   sourcebans.sb_bans  
WHERE  removetype IS NULL
       AND removedon IS NULL
       AND reason NOT LIKE '%[FragSleuth] Duplicate account%'
       AND
           (
                ip IN (SELECT DISTINCT ip
                       FROM   fragsleuth.history
                       WHERE  trackingid = "ad000c3803b48190aabf382e01b957c9")
                OR
                authid IN(SELECT DISTINCT steamid
                          FROM   fragsleuth.history
                          WHERE  trackingid = "ad000c3803b48190aabf382e01b957c9")
           )

Regarding speed improvements, you should create a covering index for the removetype, removedon, ip and authid columns first. This will help but likely won't be enough as the LIKE operation is very expensive.

The last thing you should do is to check if you can change

reason NOT LIKE '%[FragSleuth] Duplicate account%'

into something else. Can you for example eliminate the leading % so it can at least do a much faster match? Depends of course on what exactly those columns store.