MySQL Query returns invalid rows and is very slow

Question

I am writing a request to select a player’s prohibitions from another table, but, firstly, its very slow reception takes about 7-14 seconds, and secondly, it returns invalid lines.

The first request 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 few screenshots to show what I mean: First request Second request

In screenshot 1, you can see that its return string, where removeon and type of deletion are not null, when I asked the query to only return strings with NULL.

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

Any help would be greatly appreciated.

thanks

Edit: help overwhelms me, thanks to @maraca, @Skorpioh and @Adam Silenko, the request time is less than a second!

Answer 1

The query returns strings that are not NULL because they are interpreted as (... AND ... AND ... ) OR ... instead of ... AND ... AND ( ... OR ...)

So, you need to add curly braces, and DISTINCT not required either:

 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 2

and have a higher priority than ... You need to index your np tables. add an index to the tracking field in fragsleuth.history if you don't have

Most likely, you can do it faster using one additional request, but I'm not sure about that.

 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 3

You have a problem with the preventive operand here and why it ends up having a result where removeetype / removedon is not null.

If you check http://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html , you will see that And has a higher priority than OR, which means that your request will run all predicates glued together with the "AND" operator, and only after that the value OR means that you will see results in which the author is a match, and the rest does not matter anymore.

If I am not mistaken, the following 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") ) 

As for speed improvements, you should first create a coverage index for the removeetype, removedon, ip, and authid columns. This will help, but most likely will not be enough, as LIKE surgery is very expensive.

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

Reason DO NOT LIKE '% [FragSleuth] Duplicate Account%'

into something else. Can you, for example, eliminate% leadership so that he can at least play a much faster match? Depending on what exactly these columns are stored.