What is query optimization?
Query optimization is the process a database uses to choose the fastest way to run a query. The database analyzes possible strategies and picks the one that reads the least data and does the least work.
What is a query execution plan?
An execution plan is the step-by-step strategy the database decides to follow to retrieve the data requested in a query.
Why do execution plans matter?
They reveal exactly how the database runs your query so you can see whether it scans tables
What is a full table scan?
A full table scan happens when the database reads every row in a table because it cannot find a faster way to locate the requested data.
Why are full table scans slow?
Because the database must examine every row even if you only need a few results.
What is an index in SQL?
An index is a sorted data structure that allows the database to quickly locate rows without scanning the entire table.
How does an index speed up queries?
Instead of searching row by row the database jumps directly to where the desired values exist.
What is a clustered index?
A clustered index determines the physical order of rows in a table
What is a non-clustered index?
A non-clustered index is a separate structure that stores indexed values along with pointers to the actual rows in the table.
What is index selectivity?
Selectivity describes how unique values are in an indexed column. Highly unique values make indexes more effective.
Why are indexes sometimes ignored by the database?
If a query returns a large percentage of the table the database may decide scanning the table is faster than using the index.
What is a composite index?
A composite index indexes multiple columns together so queries filtering by those columns run faster.
What is index order importance?
In composite indexes the order of columns matters because the database uses the leftmost columns first.
What is index fragmentation?
Fragmentation happens when indexed data becomes scattered over time
What is partitioning in databases?
Partitioning splits a large table into smaller physical pieces so queries can scan only the relevant partitions.
Why does partitioning improve performance?
Because the database reads far less data when it can skip irrelevant partitions.
What is partition pruning?
Partition pruning occurs when the database automatically ignores partitions that do not match the query filter.
What is a hash join?
A hash join builds a hash table from one dataset and quickly matches rows from another dataset using that structure.
What is a nested loop join?
A nested loop join compares rows from one table to rows in another table repeatedly
What is a merge join?
A merge join works on sorted datasets and efficiently merges them by comparing rows in order.
Why are joins expensive operations?
Because the database must match rows between tables which can involve scanning and comparing many rows.
What is predicate pushdown?
Predicate pushdown means filtering data as early as possible so later stages process fewer rows.
Why should filters be applied early in queries?
Because reducing the number of rows early dramatically decreases the amount of work the database must do later.
What is a covering index?
A covering index contains all the columns needed for a query so the database does not need to read the main table at all.
