MySQL Optimization: EXPLAIN "Extra" column contains "Using Where"
So I always thought that seeing "Using Where" in the Extra column was a good thing. However, I was planning a brown bag lunch for my coworkers on intepreting EXPLAIN and now I'm not so sure. The MySQL doc says this in the notes about "Using Where":
A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. Even if you are using an index for all parts of a WHERE clause, you may see Using where if the column can be NULL.
This leads me to believe that even if my WHERE clause only contains parts of the index, that MySQL will still examine rows if the columns can have NULL values.
Is that true? If so, should I change the columns to not include NULL if I don't need it? Will I see a speed boost?
Answer
Nullable columns do have overhead, because of the extra need to check for the null condition. if a column doesn't have to be null, or your requirements don't allow for null, then definitely make the column not null.
As for the indexes, depends on the index construction. If the index is defined with (a,b,c) and you're using b,c in your where clause, this index cannot be used as a is not in play.