EXPLAIN
Synopsis
Use the EXPLAIN statement to show the execution plan for a statement.
Syntax
Diagram
Grammar
explain ::= EXPLAIN { select | update | insert | delete }
Semantics
Where the target statement is one of the following: SELECT, UPDATE, INSERT, or DELETE.
Examples
Create the keyspace, tables and indexes.
Setup Table and indexes
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.
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.
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.
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.
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.
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.
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.
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')
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')
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
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
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')
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
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')