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?

using index is when your where condition is covered by the indexed columns and it potentially does not need to scan the entire table. Using index condition is when the 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. The 2nd approach is called index push down. But ofcourse query with all covering index is better. — Abhik Chakraborty
– Abhik Chakraborty, Commented Feb 27, 2015 at 7:29
@AbhikChakraborty Then 'Using where;' in 'Using where; Using index' meaning that where condition is covering on indexed columns and not scanning the table? — chris
– chris, Commented Feb 27, 2015 at 7:36
Yes 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. — Abhik Chakraborty
– Abhik Chakraborty, Commented Feb 27, 2015 at 7:38

chris · Accepted Answer · 2015-03-04 17:20:52Z

17

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.

answered Mar 4, 2015 at 17:20

chris

7542 gold badges7 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

egraldlo Over a year ago

Does 'index condition pushdown' support update statement? From the source code, there is no conclude that update can use index condition pushdown.

Angel · Accepted Answer · 2018-06-18 10:03:09Z

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

answered Jun 18, 2018 at 10:03

Angel

371 bronze badge

Comments

Liang · Accepted Answer · 2024-08-02 02:27:50Z

0

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

"Using where; Using index" indicates that all the data you need are in the index.
"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.

answered Aug 2, 2024 at 2:27

Liang

78610 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related