Not the answer you need?
Register and ask your own question!

USE INDEX (PRIMARY) is much faster, even when not looking for anything in that column

steve@roveridx.com[email protected] EntrantCurrent User Role Beginner
Scenario:
  • `table_of_data` has 1 million rows
  • primary_col is the PRIMARY key. It is not AUTO_INCREMENT'ed
  • each column in the WHERE clause is indexed.
    ​​​
Trying to understand why a query that is forced to use the Primary key is 3 times faster, when the query is not looking for anything the Primary key column:
SELECT SQL_NO_CACHE primary_col, secondary_col
FROM table_of_data
WHERE col_3 = 'car'
AND col_4 = 'red ferarri'
AND col_5 IN ("2 seater")
AND col_6 IN ("Active", "Transferred", "New")
Executes in 1.5 seconds in a table of 1M rows.

But this same query, forcing MySQL to use the PRIMARY key (`primary_col`) index, is 3 times faster, even though the WHERE clause isn't looking for anything in the `primary_col` column:
SELECT SQL_NO_CACHE primary_col, secondary_col
FROM table_of_data
USE INDEX (PRIMARY)
WHERE col_3 = 'car'
AND col_4 = 'red ferarri'
AND col_5 IN ("2 seater")
AND col_6 IN ("Active", "Transferred", "New")

Executes in .45 seconds in a table of 1M rows.
Is there some magic to the PRIMARY key that I'm not understanding?

Thank you
Steve

Comments

  • Will_EdwardsWill_Edwards Entrant Current User Role Beginner
    What is the output of EXPLAIN SELECT ... for both versions of the query?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.