What does MySQl explain Extra "Using where" really mean?

Question

According to the MySQL documentation, Using where means: A WHERE clause is used to restrict which rows to match against the next table or send to the client.

As I understand, it means if your sql statement has a where condition, a 'Using where' will appear in your explain Extra information. While as my experience, it seems mean MySQL storage engine finds that the index couldn't cover some column, and will have to retrieve row data. For example:

Could anyone explain the real meaning of Using where?

Selfpollination

Answer 1

I've been using MySQL since 2001.

I've been a consultant for MySQL optimization.

I've developed and delivered MySQL training and conference presentations on query optimization, including interpreting the EXPLAIN output.

I've spoken to engineers who work on the MySQL query optimizer code, and asked them specifically "what does 'Using where' mean in the EXPLAIN output?"

I have attempted to read the MySQL code myself (I do know how to read and write C code).

I still can't understand it.

My current belief is that MySQL reports 'Using where' inconsistently. Sometimes it means that the expression in a WHERE clause must be evaluated to provide secondary filtering to examined rows after the index search has matched rows to examine.

But I believe there are cases of queries that don't fit this explanation. Other times it seems to mean only that the WHERE clause has an expression, even if that expression is being evaluated by an index lookup.

I suspect that there are simply different code paths in the query optimizer that output 'Using where' using different criteria. But I don't have proof of that.

So now I just ignore 'Using where'. It doesn't guide me when I'm trying to optimize queries. I can't use it to inform me that the query is well-optimized or poorly-optimized.

Aught