Basic Indexing

This section builds on the examples that you began in Working with complex data.

Create Index

Creates an index on column or columns of a table to speed up searches and queries.

Example 9-1 Create index

sql-> create index idx_income on Persons (income);
sql-> create index idx_age on Persons (age);

Explanation:

These queries create two indexes idx_income and idx_age on the Persons table.

Output: 
Statement completed successfully
Statement completed successfully
Let us consider the SELECT query below. Here, both the indexes are applicable. The indexes allow the query processor to instantly jump to the specific subrange of records matching your filters, avoiding a slow and costly scan of every row in the table.
sql-> SELECT * from Persons WHERE income > 10000000 and age < 40;
sql-> mode LINE
Query output mode is LINE
sql-> create index idx_income on Persons (income);
Statement completed successfully
sql-> create index idx_age on Persons (age);
Statement completed successfully
sql-> SELECT * from Persons
WHERE income > 10000000 and age < 40;

 > Row 0                                              
 +-------------+--------------------------------------------+
 | id          | 3                                          |
 +-------------+--------------------------------------------+
 | firstname   | John                                       |
 +-------------+--------------------------------------------+
 | lastname    | Morgan                                     |
 +-------------+--------------------------------------------+
 | age         | 38                                         |
 +-------------+--------------------------------------------+
 | income      | 100000000                                  |
 +-------------+--------------------------------------------+
 | lastLogin   | 2016-11-29T08:21:35.4971                   |
 +-------------+--------------------------------------------+
 | address     | street                   | 187 Aspen Drive |
 |             | city                     | Middleburg      |
 |             | state                    | FL              |
 |             | zipcode                  | NULL            |
 |             | phones                                     |
 |             |     type                 | work            |
 |             |     areacode             | 305             |
 |             |     number               | 1234079         |
 |             |                                            |
 |             |     type                 | home            |
 |             |     areacode             | 305             |
 |             |     number               | 2066401         |
 +-------------+--------------------------------------------+
 | connections | 1                                          |
 |             | 4                                          |
 |             | 2                                          |
 +-------------+--------------------------------------------+
 | expenses    | food                     | 2000            |
 |             | gas                      | 10              |
 |             | travel                   | 700             |
 +-------------+--------------------------------------------+

1 row returned

Show Index

Provides the list of indexes present on the specified table.

Example 9-2 Show index

sql-> show indexes on Persons;

Explanation:

The query lists all the indexes on the Persons table.

Output: 
indexes  
idx_age  
idx_income

If you want the output to be in JSON format, you can specify the optional AS JSON.

Example 9-3 Show index in JSON format

sql-> show as json indexes on Persons;

Explanation:

The query lists all the indexes on the Persons table in JSON format.

Output: 
{"indexes" : ["idx_age","idx_income"]}

Describe Index

Shows the detailed metadata about a specific index.

Example 9-4 Describe index

sql-> DESCRIBE INDEX idx_age ON Persons;

Explanation:

This query displays the detailed definition and metadata of the idx_age index, including its type and the fields it covers

Output: 

 +---------+---------+-----------+----------+--------+--------------+-------------+
 |  table  |  name   |   type    | multiKey | fields | declaredType | description |
 +---------+---------+-----------+----------+--------+--------------+-------------+
 | Persons | idx_age | SECONDARY | N        | age    |              |             |
 +---------+---------+-----------+----------+--------+--------------+-------------+

If you want the output to be in JSON format, you can specify the optional AS JSON.

Example 9-5 Describe index in JSON format

sql-> DESCRIBE AS JSON INDEX idx_age ON Persons;

Explanation:

This query displays the detailed definition and metadata of the idx_age index, including its type and the fields it covers, in JSON format.

Output: 
{
  "name" : "idx_age",
  "type" : "secondary",
  "fields" : ["age"],
  "withNoNulls" : false,
  "withUniqueKeysPerRow" : false
}

Drop Index

Removes or deletes the index from the table.

Example 9-6 Drop index

sql-> drop index idx_age on Persons;

Explanation:

This query deletes the idx_age index from the Persons table.

Output: 
Statement completed successfully

Classification of Indexes

Indexes are categorized based on the number of fields they cover, their relationship to the table schema, and the ratio of index entries generated per table row.

Table 9-1 Classification of indexes

Category Indexing Method Definition Example
Fields Single Field Index An index that is created on only one field of a table. 
CREATE INDEX idx_last ON Persons(lastName);
  Composite Index An index that is created on more than one field of a table. 
CREATE INDEX idx_fullname ON Persons(lastName, firstName);
Schema Fixed Schema Index Built on predefined, strongly-typed columns. 
CREATE INDEX idx_age ON Persons(age); (where age is an INTEGER)
  Schema-less Index Built on specific paths within a JSON field using a type cast. 
CREATE INDEX jsonindex2 ON UserInfo ( info.address.street AS ANYATOMIC);
Entries Simple Index A 1:1 mapping where one table row creates exactly one index entry. 
CREATE INDEX idx_income ON Persons(income);
  Multikey Index A 1:N mapping where one row (with a collection) creates multiple index entries. 
CREATE INDEX idx_connections ON Persons(connections[]); (indexes each connection in an array)
For more details, see index classification.