All conditions that negate something are basically impossible for the database to optimize.
Examples are your IS NOT NULL or col1 != ‘something’ etc.
The reason is that you are telling the database to find all records where you don’t have a match.
And the only way the DBMS can do this is by going through all records and throw away the once that doesn’t match.
But if you are issuing a query where you say that you want all records matching a certain expression then the DBMS can use an index because it can jump to the middle of the index and immediately tell (since the records are sorted) if what you are searching for is before or after the current record.
And then split it further and re-perform that operation until it finds the record that you are after.
Don’t use NULL values unless you need to.
Since NULL values are a second dimension they are much harder to work with.
Rewrite your query to search for a certain value or values and not the absence of a value.
If I want to return all records except the records that are 0 I can write that condition as:
… WHERE data > 0;
… WHERE data != 0;
And in the first expression I’m saying what I want and in the second I’m negating what I don’t want.