Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 566
chris Avatar asked Feb 27 '15 07:02

chris


People also ask

What is using index condition?

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows.

How does MySQL decide which index to use?

If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index). If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.

What does using WHERE mean?

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.

Why index is not used in MySQL?

The Benefits and Drawbacks of Using Indexes in MySQL Indexes consume disk space. Indexes degrade the performance of INSERT, UPDATE and DELETE queries – when data is updated, the index needs to be updated together with it. MySQL does not protect you from using multiple types of indexes at the same time.


1 Answers

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.

like image 147
chris Avatar answered Sep 24 '22 11:09

chris