Indexing
Indexes help the database locate rows quickly without scanning the entire table.
If we are performing order by operation on a column which has a index on it, the database does not run a sorting operation on the data since the index is already sorted.
Query Execution Plan
A Query Execution Plan (QEP) is the strategy chosen by the database optimizer to execute a SQL query. For the same query, the database may choose different execution plans depending on the table size, available indexes, data distribution, statistics and join conditions.
Q1. You have a table storing market price curves.
MARKET_PRICE
------------
curve_id
trade_date
delivery_date
price
Initially, a query on a table with 100k took 100ms. After the table grew to 10M rows, the same
query now takes 5 seconds. What could be the probable reason?
SELECT *
FROM market_price
WHERE curve_id = 101
AND trade_date = CURRENT_DATE;
A. Most Probable Reason: Table Size is Growing Daily
If the amount of data being read everyday increases gradually, the runtime of query execution is expected to increase with time.
Possible solutions:
-
Add Appropriate Indexes: If an appropriate index is not present, the optimizer may perform a full table scan, causing query execution time to increase with table size. For example, create a composite index on the
curve_idandtrade_datecolumns. -
Archive Historical Data: Move old market prices that are rarely accessed to an archive table or data lake.
-
Partition Table: Partition the table by
trade_date(yearly or monthtly).
Q2. A query that consistently executed in 100 ms suddenly started taking 5 seconds,
even though no application code changes were deployed. What could be the probable reason?
A. Most Probable Reason: Query Execution Plan Changed
An index becomes less effective when many rows contain the same value. Less selective index.
Possible solutions could be to use composite indexes
Q2. How to optimize query performance?
Indexing
Q4. Write queries have started taking more time.