---
title: "EXPLAIN"
url: "https://docs.yugabyte.com/stable/api/ycql/explain/"
---

# EXPLAIN

Use the EXPLAIN statement to show the execution plan for a YCQL statement.

## Synopsis

Use the `EXPLAIN` statement to show the execution plan for a statement.

## Syntax

### Diagram

EXPLAIN[select](/stable/api/ycql/grammar_diagrams#select)[update](/stable/api/ycql/grammar_diagrams#update)[insert](/stable/api/ycql/grammar_diagrams#insert)[delete](/stable/api/ycql/grammar_diagrams#delete)

### Grammar

```
explain ::= EXPLAIN { select | update | insert | delete }
```

## Semantics

Where the target statement is one of the following: [SELECT](/stable/api/ycql/dml_select/ "SELECT"), [UPDATE](/stable/api/ycql/dml_update/ "UPDATE"), [INSERT](/stable/api/ycql/dml_insert "INSERT"), or [DELETE](/stable/api/ycql/dml_delete/ "DELETE").

## Examples

Create the keyspace, tables and indexes.

### Setup Table and indexes

```CQL
cqlsh> CREATE KEYSPACE IF NOT EXISTS imdb;
cqlsh> CREATE TABLE IF NOT EXISTS imdb.movie_stats (
           movie_name text,
           movie_genre text,
           user_name text,
           user_rank int,
           last_watched timestamp,
           PRIMARY KEY (movie_genre, movie_name, user_name)
    ) WITH transactions = { 'enabled' : true };
cqlsh> CREATE INDEX IF NOT EXISTS most_watched_by_year
      ON imdb.movie_stats((movie_genre, last_watched), movie_name, user_name)
      INCLUDE(user_rank);
cqlsh> CREATE INDEX IF NOT EXISTS best_rated
      ON imdb.movie_stats((user_rank, movie_genre), movie_name, user_name)
      INCLUDE(last_watched);
```

Insert some rows.

```CQL
cqlsh> USE imdb;
cqlsh:imdb> INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m1', 'g1', 'u1', 5, '2019-01-18');
cqlsh:imdb> INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m2', 'g2', 'u1', 4, '2019-01-17');
cqlsh:imdb> INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m3', 'g1', 'u2', 5, '2019-01-18');
cqlsh:imdb> INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m4', 'g1', 'u1', 2, '2019-02-27');
```

### Explain query plans

If movie\_genre, or movie\_genre & movie\_name, or movie\_genre & movie\_name & user\_name are specified, the query should be served efficiently from the primary table.

```CQL
cqlsh:imdb> EXPLAIN SELECT *
            FROM movie_stats
            WHERE movie_genre = 'g1';

QUERY PLAN
----------------------------------------
 Range Scan on imdb.movie_stats
   Key Conditions: (movie_genre = 'g1')
```

If movie\_genre & last\_watched are specified, then the query should be served efficiently from the `most_watched_by_year` index.

```CQL
cqlsh:imdb> EXPLAIN SELECT *
            FROM movie_stats
            WHERE movie_genre = 'g1' and last_watched='2019-02-27';

QUERY PLAN
--------------------------------------------------------------------------
 Index Only Scan using imdb.most_watched_by_year on imdb.movie_stats
   Key Conditions: (movie_genre = 'g1') AND (last_watched = '2019-02-27')
```

If user\_rank and movie\_genre are specified then the query should be served efficiently from the `best_rated` index.

```CQL
cqlsh:imdb> EXPLAIN SELECT *
            FROM movie_stats
            WHERE movie_genre = 'g2' and user_rank=5;

QUERY PLAN
--------------------------------------------------------------
 Index Only Scan using imdb.best_rated on imdb.movie_stats
   Key Conditions: (user_rank = '5') AND (movie_genre = 'g2')
```

Create non-covering index.

```CQL
cqlsh:imdb> DROP INDEX best_rated;
cqlsh:imdb> CREATE INDEX IF NOT EXISTS best_rated
            ON imdb.movie_stats((user_rank, movie_genre), movie_name, user_name);
```

2-Step select. Using Index Scan as opposed to Index Only Scan.

```CQL
cqlsh:imdb> EXPLAIN SELECT *
            FROM movie_stats
            WHERE movie_genre = 'g2' and user_rank=5;

 QUERY PLAN
--------------------------------------------------------------
 Index Scan using imdb.best_rated on imdb.movie_stats
   Key Conditions: (user_rank = '5') AND (movie_genre = 'g2')
```

Note

**INDEX SCAN**: Filters rows using the index and then fetches the columns from the main table.

**INDEX ONLY SCAN**: Returns results by only consulting the index.

### Other EXPLAIN SELECT types

`QLName()` for these expressions is not supported.

```CQL
cqlsh:imdb> EXPLAIN SELECT * FROM movie_stats where movie_genre in ('g1', 'g2');

 QUERY PLAN
-------------------------------------------
 Range Scan on imdb.movie_stats
   Key Conditions: (movie_genre IN 'expr')
```

```CQL
cqlsh:imdb> EXPLAIN SELECT COUNT(*) FROM movie_stats  WHERE movie_genre = 'g2' and user_rank=5;

 QUERY PLAN
--------------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using imdb.best_rated on imdb.movie_stats
         Key Conditions: (user_rank = '5') AND (movie_genre = 'g2')
```

```CQL
cqlsh:imdb> EXPLAIN SELECT * FROM movie_stats  WHERE movie_genre = 'g2' and user_rank = 5 LIMIT 5;

 QUERY PLAN
--------------------------------------------------------------------
 Limit
   ->  Index Only Scan using imdb.best_rated on imdb.movie_stats
         Key Conditions: (user_rank = '5') AND (movie_genre = 'g2')
```

### INSERT example

```CQL
cqlsh:imdb> EXPLAIN INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m4', 'g1', 'u1', 2, '2019-02-27');

 QUERY PLAN
----------------------------
 Insert on imdb.movie_stats
```

### DELETE examples

```CQL
cqlsh:imdb> explain delete from movie_stats  where movie_genre = 'g1' and movie_name = 'm1';

 QUERY PLAN
----------------------------------------------
 Delete on imdb.movie_stats
   ->  Range Scan on imdb.movie_stats
         Key Conditions: (movie_genre = 'g1')
         Filter: (movie_name = 'm1')
```

```CQL
cqlsh:imdb> explain delete from movie_stats  where movie_genre = 'g1';

 QUERY PLAN
----------------------------------------------
 Delete on imdb.movie_stats
   ->  Range Scan on imdb.movie_stats
         Key Conditions: (movie_genre = 'g1')
```

### UPDATE example

```CQL
cqlsh:imdb> EXPLAIN UPDATE movie_stats SET user_rank = 1 WHERE movie_name = 'm1' and movie_genre = 'g1' and user_name = 'u1';

 QUERY PLAN
---------------------------------------------------------------------------------------------
 Update on imdb.movie_stats
   ->  Primary Key Lookup on imdb.movie_stats
         Key Conditions: (movie_genre = 'g1') AND (movie_name = 'm1') AND (user_name = 'u1')
```

## See also

- [INSERT](/stable/api/ycql/dml_insert "INSERT")
- [SELECT](/stable/api/ycql/dml_select/ "SELECT")
- [UPDATE](/stable/api/ycql/dml_update/ "UPDATE")
- [DELETE](/stable/api/ycql/dml_delete/ "DELETE")