SQL Index Tuning – Difference Between Index Scan and Index Seek in Databases

index-tuningMySQLoraclepostgresqlsql server

Let's say I have a table:

CREATE TABLE transactions (
    id NUMERIC(20, 0) PRIMARY_KEY,
    account_id VARCHAR(512),
    ...
    transaction_date TIMESTAMP
);

So with SQL Server, an index:

CREATE NONCLUSTERED INDEX my_index ON transactions (account_id, transaction_date, id);

and a query:

SELECT id FROM transactions 
WHERE account_id = '123' 
AND transaction_date > '2023-01-01'

performs an Index Seek, meaning that the query is fulfilled from the index, not making a reference to the table, because all the data it needs is present on the table. If my index were:

CREATE NONCLUSTERED INDEX my_index ON transactions (account_id, transaction_date);

query would go to the table to fulfill the query (Index Scan). Which of the following databases:

  1. Oracle
  2. MySQL
  3. PostgreSQL

support the above behavior?

Best Answer

For that query and that composite index, MySQL will "seek" to ['123', '2023-01-01'], then "scan" until >123.

And, because the PRIMARY KEY is implicitly part of any secondary key,

 INDEX(account_id, transaction_date)

will have id on the end. Hence, that query will be performed entirely in the INDEX's BTree.

Note that some other vendors do not include the PK; hence cannot perform the query query entirely in the 2-column index.

That is, in MySQL, these are equivalent, at least for the query in question:

 INDEX(account_id, transaction_date)
 INDEX(account_id, transaction_date, id)

(MySQL does not have the NONCLUSTERED keyword -- The PK is clustered; all other indexes are non-clustered.)

Related Question