Identifying Index Usage (EXPLAIN)

This section assumes you have run the steps at Examples of Debugging a Slow Query.

For purpose of this discussion, let’s say you have a query and this query is running less than optimally:

SELECT firstname, lastname, playerId FROM Players WHERE firstname = 'Mike';

What’s going on? The first step will be to determine whether or not the query is using the index you expect it to use. This can be done with the EXPLAIN command (see EXPLAIN). Given any SQL query (sqlquery), NuoDB SQL will accept the syntax “EXPLAIN sqlquery”. This will display the query plan produced by NuoDB’s query optimizer. The query plan shows what decisions the NuoDB optimizer made to retrieve the data for the query result set. With the plan in hand you can make changes to the query or the database structure (add/remove indexes) to improve query performance.

The output from the EXPLAIN command is the same as the value specified in the SYSTEM.QUERYSTATS EXPLAIN column.

NuoDB EXPLAIN can also be used on the DML statements UPDATE, REPLACE, and DELETE. The EXPLAIN function will display the query plan that NuoDB’s optimizer produced for selecting the rows to be manipulated by the DML statement.

Using nuosql, EXPLAIN can be invoked on a SELECT statement against the PLAYERS table, as follows:

# Select Players with last name "Brooke".
``SELECT firstname, lastname, playerID FROM players WHERE lastname='Brooke';``
 FIRSTNAME  LASTNAME  PLAYERID
 ---------- --------- ---------
    Bob      Brooke   brookbo01

# Explain the previous query (see EXPLAIN for a full description of the NuoDB
# Explain output).
# Under "List", we see the fields involved in the query.
# Under "Boolean sieve" the Explain plan shows the WHERE clause criteria that
# must be valid for the row to be included in the query result set.
# "Eql" shows we are testing equality between the field LASTNAME and "Brooke".
# "Exhaustive" means the optimizer is scanning the entire table to find the rows
# to retrieve for the query result set.``
EXPLAIN SELECT firstname, lastname, playerID FROM players WHERE lastname='Brooke';``
 Select
  List
    Field HOCKEY.PLAYERS.FIRSTNAME (1)
    Field HOCKEY.PLAYERS.LASTNAME (1)
    Field HOCKEY.PLAYERS.PLAYERID (1)
  Boolean sieve
    Eql
      Field HOCKEY.PLAYERS.LASTNAME (1)
      "Brooke" (varchar)
    Exhaustive HOCKEY.PLAYERS  (1)

# Now show the Explain of a query with a WHERE clause using an indexed column,
# FIRSTNAME. This time the Explain output shows an "Inversion" scan instead
# of an "Exhaustive" scan.
# this means the optimizer choose to scan the index IDX_PLAYERS_FIRSTNAME to
# retrieve the data for the query result set, rather than doing a full or
# Exhaustive table scan.``
EXPLAIN SELECT firstname, lastname, playerID FROM players WHERE firstname='Kurt';``
 Select
  List
    Field HOCKEY.PLAYERS.FIRSTNAME (1)
    Field HOCKEY.PLAYERS.LASTNAME (1)
    Field HOCKEY.PLAYERS.PLAYERID (1)
  Boolean sieve
    Eql
      Field HOCKEY.PLAYERS.FIRSTNAME (1)
      "Kurt" (varchar)
    Inversion HOCKEY.PLAYERS  (1)
      Bitmap index IDX_PLAYERS_FIRSTNAME
        "Kurt" (varchar)