Covering indexes

Explore covering indexes in YugabyteDB using YCQL

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.


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

For additional information on creating indexes, see CREATE INDEX.


Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

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

  1. Create a sample HR keyspace as follows:

    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';
     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';
     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';
     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';
    Index Only Scan using HR.index_employees_department_nm on HR.employees
      Key Conditions: (department = 'Sales')