Menu
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.
566
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.
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.
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.
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.
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.
147
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
