Scenario:
[LIST]
[]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.
[/LIST] 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:
[INDENT]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”)
[/INDENT]
[INDENT]Executes in [COLOR=#FF0000]1.5 seconds in a table of 1M rows.[/INDENT]
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:
[INDENT]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 [COLOR=#FF0000].45 seconds in a table of 1M rows.
[/INDENT]
Is there some magic to the PRIMARY key that I’m not understanding?
Thank you
Steve