ℹ️ Select 'Choose Exercise', or randomize 'Next Random Exercise' in selected language.

Choose Exercise:
Timer 00:00
WPM --
Score --
Acc --
Correct chars --

Implement Covering Index for Performance

SQL MySQL

Goal -- WPM

Ready
Exercise Algorithm Area
1CREATE TABLE orders (

2order_id INT AUTO_INCREMENT PRIMARY KEY,

3customer_id INT NOT NULL,

4order_date DATE NOT NULL,

5order_amount DECIMAL(10, 2),

6shipping_address VARCHAR(255),

7order_status VARCHAR(50)

8);

9

10-- Create a covering index for queries filtering by order_date and selecting specific columns.

11-- The index includes order_date for filtering, customer_id and order_date for selection,

12-- and order_id (as it's the primary key, implicitly included or can be explicitly added).

13-- For a true covering index that includes columns not in the key, use the INCLUDE clause.

14-- MySQL 5.6+ supports INCLUDE clause for secondary indexes.

15

16CREATE INDEX idx_orders_date_cust_date

17ON orders (order_date, customer_id, order_id)

18INCLUDE (order_date); -- Explicitly include order_date again for clarity, though it's in the key.

19

20-- The query this index is designed to cover:

21SELECT

22o.order_id,

23o.customer_id,

24o.order_date

25FROM

26orders o

27WHERE

28o.order_date >= '2023-01-01'

29ORDER BY

30o.order_date;

31

32-- Explanation of Covering Index:

33-- The index `idx_orders_date_cust_date` is designed to cover the query.

34-- 1. `order_date` is the first column in the index, allowing MySQL to efficiently

35--    find rows matching `o.order_date >= '2023-01-01'`. This is called index seek.

36-- 2. `customer_id` and `order_id` are also included in the index key or explicitly

37--    included via `INCLUDE`. This means MySQL can retrieve `customer_id` and `order_id` directly

38--    from the index without having to access the table's data rows (index scan).

39-- 3. `order_date` is also included in the `INCLUDE` clause, which is redundant here

40--    as it's already the leading column, but demonstrates the syntax.

41-- This 'covering' of the query by the index avoids a table lookup (full row fetch),

42-- significantly speeding up query execution, especially for large tables.

43

44-- Edge Case Consideration:

45-- If the query also needed `order_amount`, the index would need to be:

46-- CREATE INDEX idx_orders_date_cust_date_amount

47-- ON orders (order_date, customer_id, order_id)

48-- INCLUDE (order_date, order_amount);

49-- Without `order_amount` in the index (either key or INCLUDE), MySQL would still need

50-- to perform a table lookup for that column.
Algorithm description viewbox

Implement Covering Index for Performance

Algorithm description:

This scenario focuses on creating and utilizing a covering index in MySQL to optimize query performance. A covering index includes all the columns required by a query, allowing the database to retrieve all necessary data directly from the index without accessing the table's data rows. This is a powerful technique for speeding up read-heavy workloads, especially for frequently executed queries.

Algorithm explanation:

A covering index is a secondary index that contains all the columns needed to satisfy a query. In this case, the `CREATE INDEX` statement defines an index on `(order_date, customer_id, order_id)` and uses the `INCLUDE` clause for `order_date`. The `order_date` is used for filtering in the `WHERE` clause, and `order_id`, `customer_id`, and `order_date` are selected. MySQL can satisfy the query entirely from this index (index seek on `order_date`, then retrieval of `customer_id` and `order_id` from the index structure itself). This avoids a costly table lookup. The `INCLUDE` clause is crucial for adding columns to the index that are not part of the leading columns but are needed by the query. Time complexity for the query becomes O(log N) or O(M) where M is the number of rows matching the filter, depending on index structure and selectivity, instead of O(N) or O(N log N) for a table scan. Space complexity is for the index itself.

Pseudocode:

1. Define a table `orders` with columns: `order_id`, `customer_id`, `order_date`, `order_amount`, etc.
2. Create a `CREATE TABLE` statement for `orders`.
3. Create a `CREATE INDEX` statement for a covering index.
4. The index should include `order_date` as the leading column for filtering.
5. Include `customer_id` and `order_id` in the index key or using the `INCLUDE` clause.
6. Write a `SELECT` query that retrieves `order_id`, `customer_id`, and `order_date`.
7. The `WHERE` clause of the `SELECT` query should filter on `order_date`.
8. Ensure the `SELECT` list and `WHERE` clause are fully covered by the index.