MySQL Optimization: EXPLAIN "Extra" Column Contains "Using Where"

Question

Therefore, I always thought that seeing “Using Where” in the “Advanced” column is good. However, I was planning a brown bag lunch for my EXPLAIN introspection colleagues, and now I'm not sure. The MySQL document talks about this in the notes on "Using Where":

The WHERE clause is used to limit which rows match the following table or send to the client. If you are not specifically planning to retrieve or check all the rows from the table, you might have something wrong in your query if the Extra value is not used where and the join type of the table is ALL or index. Even if you use an index for all parts of the WHERE clause, you can see where to use it if the column can be NULL.

This makes me think that even if the WHERE clause contains only parts of the index, MySQL will still check the rows if the columns can be NULL.

It's true? If so, should the columns be changed so that they do not include NULL if I do not need it? Will I see speed acceleration?

Answer 1

Unnecessary columns have overhead, due to the additional need to check for a null condition. if the column should not be null, or your requirements do not allow null, then be sure to make the column not null .

As for indices, it depends on the design of the index. If the index is defined using (a,b,c) and you use b,c in the where clause, that index cannot be used since a not in the game.