A table has millions of records and searches are becoming slow. What DBMS feature would you consider first?
Interview preparation resource from Gate Smashers.
First consider adding an index on the columns used by the slow query—especially those in selective WHERE conditions, JOINs, and sometimes ORDER BY or GROUP BY. Use EXPLAIN (or the DBMS execution plan) to verify index usage. Keep in mind selectivity, composite indexes when multiple columns are used together, and that an index is a strong first consideration but not an automatic cure; other causes (poor joins, returning too much data, outdated statistics, locking, insufficient memory) can also cause slowness.
Primary feature to consider
The first DBMS feature to consider is an index on the columns used by the slow query. Without a useful index the DBMS may perform a full table scan and examine millions of rows even when only a small number match the query. An index creates an additional search structure that helps the DBMS reach matching rows much more quickly.
Which columns to index
Focus indexing effort on columns that the query actually uses. This includes columns in WHERE conditions, columns used to join tables, and sometimes columns used for ORDER BY or GROUP BY.
- WHERE conditions: Index columns used in selective WHERE predicates.
- JOIN columns: Index columns that are used in joins between tables.
- Sorting/Grouping: Consider indexes on columns used in ORDER BY or GROUP BY where appropriate.
- Composite indexes: Use composite (multi-column) indexes when multiple columns are frequently used together in queries.
How to verify and choose
Use the execution plan (for example EXPLAIN) to verify whether the DBMS is using the index and to identify where the real cost occurs. The correct index depends on the actual query pattern; an index that looks reasonable may not be used if it doesn't match how the query is written or if statistics are outdated.
- Execution plan: Run EXPLAIN to see if the index is used and to find hot spots.
- Selectivity: Be aware that an index on a column with very low selectivity may provide little benefit.
Caveats and other causes of slowness
An index is a strong first consideration but not an automatic cure. Slow performance can also come from poor joins, returning too much data, outdated statistics, locking, insufficient memory, or other causes. Use profiling and the execution plan to diagnose whether indexing or other changes are needed.