SQL Indexing & Query Optimization Flashcards

Question 1

What is a SQL Index?

Accepted Answer

A database object that provides quick lookup of data in a column or columns of a table. It speeds up data retrieval operations by reducing the number of disk I/O operations required, acting like a book's index.

Question 2

What is a B-tree Index?

Accepted Answer

The most common type of index, organized as a balanced tree structure. It's efficient for range queries, equality lookups, and sorting. Each node contains key-pointer pairs, allowing for fast traversal to locate data. It's the default index type in most relational databases.

Question 3

When should you use a B-tree Index?

Accepted Answer

Ideal for columns frequently used in `WHERE` clauses, `JOIN` conditions, `ORDER BY` clauses, and `GROUP BY` clauses, especially with high cardinality (many unique values) or when range queries are common.

Question 4

What is a Hash Index?

Accepted Answer

An index that uses a hash function to map key values to locations in a hash table. It's extremely fast for equality lookups (`=`) because it directly computes the data's location, but inefficient for range queries (`>`, `<`, `BETWEEN`) or sorting.

Question 5

When should you use a Hash Index, and what are its limitations?

Accepted Answer

Best for columns with high cardinality where only equality lookups are performed. Limitations include poor performance for range queries, `ORDER BY`, and `LIKE` operations. Not all database systems support them for disk-based tables (e.g., SQL Server doesn't).

Question 6

What is a Bitmap Index?

Accepted Answer

An index that stores a bitmap (a sequence of bits) for each distinct value in the indexed column. Each bit corresponds to a row, indicating whether that row contains the value. Bitmaps can be efficiently combined using bitwise operations for complex queries.

Question 7

When should you use a Bitmap Index, and what are its limitations?

Accepted Answer

Highly effective for columns with low cardinality (few distinct values, e.g., `gender`, `status_flag`) and often used in data warehousing for complex `WHERE` clauses with multiple `AND`/`OR` conditions. Not suitable for columns with frequent updates, as updates require rebuilding many bitmaps.

Question 8

Explain the concept of a Clustered Index.

Accepted Answer

A special type of index that dictates the physical order of data rows in the table. The table's data is stored on disk in the order of the clustered index key. A table can only have *one* clustered index, as data can only be physically sorted one way.

Question 9

What are the characteristics and implications of a Clustered Index?

Accepted Answer

1. **Physical Order**: Data rows are physically sorted on disk based on the index key. 2. **One per Table**: Only one clustered index can exist per table. 3. **Primary Key Default**: Often created automatically on the `PRIMARY KEY` if not specified otherwise. 4. **Fast Retrieval**: Excellent for range queries and retrieving contiguous blocks of data. 5. **Insert/Update Cost**: Can be more expensive for inserts/updates if data needs to be physically reordered.

Question 10

Explain the concept of a Non-Clustered Index.

Accepted Answer

An index that does not dictate the physical order of data rows. It's a separate structure containing the indexed column(s) and pointers (row locators or clustered index keys) to the actual data rows in the table. The data itself remains unsorted relative to this index.

Question 11

What are the characteristics and implications of a Non-Clustered Index?

Accepted Answer

1. **Logical Order**: Stores a logical ordering of key values, separate from the data's physical order. 2. **Multiple per Table**: A table can have many non-clustered indexes. 3. **Pointer to Data**: Each entry points to the actual data row (either directly via a Row ID or via the clustered index key). 4. **Overhead**: Requires additional storage and maintenance during data modifications.

Question 12

What is a Composite Index (or Concatenated Index)?

Accepted Answer

An index created on two or more columns of a table. The order of columns in the index definition is crucial, as it affects which queries can utilize the index efficiently. For example, an index on `(col1, col2)` is different from `(col2, col1)`.

Question 13

How does column order affect a Composite Index?

Accepted Answer

For a composite index on `(col1, col2, col3)`, it can be used for queries filtering on `col1`, `(col1, col2)`, or `(col1, col2, col3)`. It generally *cannot* be used efficiently for queries filtering only on `col2` or `col3` alone, or `(col2, col3)`, due to the left-most prefix rule.

Question 14

What is Index Selectivity?

Accepted Answer

A measure of how unique the values in an indexed column (or set of columns) are. High selectivity means many unique values (e.g., `user_id`), while low selectivity means few unique values (e.g., `gender`). It's often calculated as `(number of distinct values / total number of rows)`.

Question 15

Why is Index Selectivity important for query optimization?

Accepted Answer

High selectivity indexes are generally more effective because they narrow down the result set significantly, reducing the number of rows the database needs to scan. Low selectivity indexes might not be used by the optimizer if a full table scan is deemed more efficient, as the cost of traversing the index might outweigh the benefit.

Question 16

What is the purpose of `EXPLAIN PLAN` (or `EXPLAIN`)?

Accepted Answer

A SQL command used to analyze how a database query optimizer plans to execute a given SQL statement. It provides insights into the access methods (e.g., index scan, table scan), join order, and operations performed, helping identify performance bottlenecks and understand why a query is slow.

Question 17

What is a Covering Index?

Accepted Answer

A non-clustered index that includes all the columns required by a query, both in the `SELECT` list and the `WHERE`/`ORDER BY`/`GROUP BY` clauses. This allows the database to retrieve all necessary data directly from the index without needing to access the actual table rows (avoiding a "bookmark lookup" or "key lookup").

Question 18

What is the benefit of a Covering Index?

Accepted Answer

Significantly improves query performance by eliminating the need for the database to perform additional lookups into the base table. This reduces disk I/O, improves cache hit rates, and can dramatically speed up queries, especially those with many rows or wide tables.

Question 19

Differentiate between an Index Scan and a Table Scan.

Accepted Answer

1. **Index Scan**: The database reads data by traversing an index. It's efficient when only a subset of rows or columns is needed, or when the index provides the required order. 2. **Table Scan (Full Table Scan)**: The database reads every row in the table from beginning to end. This happens when no suitable index exists, or the optimizer determines it's faster than using an index (e.g., for queries returning a large percentage of the table's rows).

Question 20

Name a common query anti-pattern related to functions on indexed columns.

Accepted Answer

Applying a function to an indexed column in the `WHERE` clause (e.g., `WHERE YEAR(order_date) = 2023`). This often prevents the database from using the index on `order_date`, forcing a table scan. Instead, rewrite as `WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'` to allow index usage.

Question 21

Name a common query anti-pattern related to the `LIKE` operator.

Accepted Answer

Using a leading wildcard with `LIKE` (e.g., `WHERE product_name LIKE '%apple%'`). This prevents the database from using an index on `product_name` because the index is sorted from the beginning of the string. `LIKE 'apple%'` (trailing wildcard) can utilize an index.

Question 22

What are the trade-offs of creating too many indexes?

Accepted Answer

1. **Increased Storage**: Indexes consume additional disk space. 2. **Slower Write Operations**: `INSERT`, `UPDATE`, and `DELETE` operations become slower because the database must also update all associated indexes. 3. **Optimizer Overhead**: The query optimizer has more indexes to consider, potentially increasing query planning time and making suboptimal choices.

Question 23

When might an index *not* be beneficial?

Accepted Answer

1. On small tables where a full table scan is faster. 2. On columns with very low cardinality (e.g., boolean flags) unless used in a bitmap index or as part of a highly selective composite index. 3. On columns that are rarely queried. 4. On columns with very frequent updates, due to the write overhead.

Question 24

What is the role of the Query Optimizer?

Accepted Answer

A component of the database management system (DBMS) responsible for determining the most efficient execution plan for a given SQL query. It considers available indexes, table statistics, join types, and other factors to minimize resource consumption (CPU, I/O) and execution time. It's the 'brain' behind query performance.

SQL Indexing & Query Optimization

Card Preview

Related Flashcard Sets

SQL Indexing, B-Trees & Query Optimization

Concurrency & Parallelism in Software Engineering

Organic Chemistry - Reaction Mechanisms & Synthesis

BIO102 - Cell Biology & Fundamental Processes