Search code examples
mysqlperformanceindexing

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

I want to know difference between Using index condition and Using where; Using index. I think both method use index to fetch first result record set, and filter using WHERE condition.

Q1. What's the difference?

Q2. Which is better?

Thank you.

Solution

  • 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.