Covering indexes

A covering index is an index that includes all the columns required by a query, including columns that would typically not be a part of an index. This is done by using the INCLUDE keyword to list the columns you want to include.

A covering index is an efficient way to perform index-only scans, where you don't need to scan the table, just the index, to satisfy the query.

Syntax

CREATE INDEX columnA_index_name ON table_name(columnA) INCLUDE (columnC);

For additional information on creating indexes, see CREATE INDEX.

Example

The following exercise demonstrates how to optimize query performance using a covering index.

1. Create a sample HR keyspace as follows:

   ycqlsh> CREATE KEYSPACE HR;
   ycqlsh> USE HR;
   

2. Create and insert some rows into a table employees with two columns id and username

   CREATE TABLE employees (
   employee_no integer PRIMARY KEY,
   name text,
   department text
   )
   WITH TRANSACTIONS = {'enabled':'true'};
   

   INSERT INTO employees(employee_no, name,department) VALUES(1221, 'John Smith', 'Marketing');
   INSERT INTO employees(employee_no, name,department) VALUES(1222, 'Bette Davis', 'Sales');
   INSERT INTO employees(employee_no, name,department) VALUES(1223, 'Lucille Ball', 'Operations');
   

3. Run a select query to fetch a row with a particular username

   SELECT name FROM employees WHERE department='Sales';
   

    name
   -------------
    Bette Davis
   

4. Run EXPLAIN on select query to show that the query does a sequential scan before creating an index

   EXPLAIN SELECT name FROM employees WHERE department='Sales';
   

    QUERY PLAN
    ----------------------------------
    Seq Scan on docs.employees
     Filter: (department = 'Sales')
   

5. Optimize the SELECT query by creating an index as follows

   CREATE INDEX index_employees_department ON employees(department);
   

   EXPLAIN SELECT name FROM employees WHERE department='Sales';
   

    QUERY PLAN
    --------------------------------------------------------------------
    Index Scan using index_employees_department on employees
     Key Conditions: (department = 'Sales')
   

   As the select query includes a column that is not included in the index, the query still reaches out to the table to get the column values.

6. Create a covering index by specifying the username column in the INCLUDE clause as follows:

   CREATE INDEX index_employees_department_nm ON employees(department) include(name);
   

   A covering index allows you to perform an index-only scan if the query select list matches the columns that are included in the index and the additional columns added using the INCLUDE keyword.

   Ideally, specify columns that are updated frequently in the INCLUDE clause. For other cases, it is probably faster to index all the key columns.

   EXPLAIN SELECT name FROM employees WHERE department='Sales';
   

    QUERY PLAN
   ----------------------------------------------------------------------------
   Index Only Scan using HR.index_employees_department_nm on HR.employees
     Key Conditions: (department = 'Sales')