Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

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

Tags:

performance

indexing

mysql

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
Sign in to Comment

Related questions

Can't add AUTO_INCREMENT on existing column because of foreign key
MySQL query to print output as CSV to standard output
CodeIgniter Transactions - trans_status and trans_complete return true but nothing being committed
How to resolve ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'?
is there a way to throttle the indexing of Mysql tables so overall performance is not impacted?
What is the best way to return enum values in MySQL?
MySQL table partition by month
Database normalization for School Management System
SQL selecting people you may know
Connecting to MySQL in C++
How to Add to MySQL Configuration on Travis CI When Not Sudo
SELECT range of integers in MySQL. Eg. 1,2,3,4,...,n;
Why to use DB::raw inside DB::select in Laravel?
Too many auto increments with ON DUPLICATE KEY UPDATE
How to convert latin1_swedish_ci data into utf8_general_ci?
mysql: connection refused when trying to connect to localhost using remote IP
Gracefully handling "MySQL has gone away"
Designing a recipe database that needs to include ingredients as well as sub-recipes
MySQL: Dramatically slower query execution if use LIMIT 1 instead of LIMIT 5
Eloquent ORM, deleted_at has no index when using soft deleting

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!