MySQL Optimization: EXPLAIN "Extra" column contains "Using Where"

Question

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?

Wanonah

i just made a test: change the column from null to not null then "using where" disappeared. – Tansy

Answer 1

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.

Mohler

Hey @Marc, thanks for the reply. I understand how multipart indexes work. I guess what I'm asking is: if I have a table with index (a,b,c) and a query like "select a,b,c from table where a=? and b=? and c=?" and column c is nullable, does MySQL still have to inspect each row, or does the index resolve the query fully? – Wanonah

it'd be able to filter partially based on a/b, but still have to do the extra work with C to check for nulls. more efficient than no index, definitely, but not as efficient if all three columns were non-null. – Mohler