MySQL - 'Using index condition' vs 'Using where; Using index'

Question

I want to know the difference between ‘Using index condition’ and ‘Using where, Using index.’ I think both methods use an index to fetch the first result record set and filter the data using the WHERE condition.

Q1. What’s the difference?

Q2. Which one is better?

Answer 1

From my perspective, they are not comparable because they are different optimize methods for different scenarios.

1. "Using where; Using index" indicates that all the data you need are in the index.
2. "Using index condition" indicates that MySQL still needs to look for other information through the table.

However, on some levels, "Using where; Using index" will be better because looking information through the table is time-consuming.

Answer 2

If you watch this link: https://dev.mysql.com/doc/refman/5.7/en/index-extensions.html

It explain that when 'Column Extra' says 'Using Index Condition', all columns in where condition are using index. If there are any columns out of index, then Column Extra say Using Where, Using Index (in this case, Mysql need look for in data row to apply where clause). It's better 'Using Index Condition'.

Answer 3

Using index condition : where condition contains indexed and non-indexed column and the optimizer will first resolve the indexed column and will look for the rows in the table for the other condition (index push down)

Using where; Using index : 'Using index' meaning not doing the scan of entire table. 'Using where' may still do the table scan on non-indexed column but it will use if there is any indexed column in the where condition first more like using index condition

Which is better? 'Using where; Using index' would be better then 'Using index condition' if query has index all covering.