Create an index

This topic describes how to create an index by using SQL statements. It also describes the prerequisites, concepts, limitations, and recommendations for creating an index, and provides some examples.

info

This topic describes how to create an index by using the CREATE INDEX statement. Multi-value indexes can be created only when you create a table. For more information about other ways to create an index, see CREATE TABLE or ALTER TABLE.

Introduction

An index is also called a secondary index. It is an optional table structure. seekdb uses a clustered index table model. For the primary key specified by users, the system automatically generates a primary key index. For other indexes created by users, they are secondary indexes. You can decide based on your business needs which fields to create indexes on to speed up queries on those fields.

For more information about indexes in seekdb, see Introduction.

Prerequisites

Before you create an index, make sure that the following conditions are met:

• You have deployed seekdb. For more information about how to deploy seekdb, see Overview.

• You have connected to seekdb.

• You have created a database. For more information about how to create a database, see Create a database.

• You have created a table. For more information about how to create a table, see Create a table.

• You have the INDEX privilege. For information about how to query the privileges of the current user, see Query the privileges of the current user. If you do not have the privilege, contact the administrator to grant the privilege. For information about how to directly grant a privilege, see Directly grant a privilege.

Limitations on index creation

• In seekdb, the name of an index must be unique within the database.

• The length of an index name cannot exceed 64 bytes.

• Limitations on unique indexes:

  • You can create multiple unique indexes in a table, but the values of the columns corresponding to each unique index must be unique.

  • If you want to ensure the global uniqueness of a combination of columns other than the primary key, you must create a global unique index.

  • When you create a local unique index, the index must contain all columns in the partitioning function of the table.

• When you create a global index, the partitioning rules of the global index do not need to be the same as or consistent with those of the table.

• Limitations on spatial indexes:

  • seekdb supports only local spatial indexes and does not support global spatial indexes.

  • The column on which you create a spatial index must be defined with the SRID attribute. Otherwise, the spatial index on this column will not take effect in subsequent queries. For more information about the SRID attribute, see Spatial reference systems (SRS).

  • You can create a spatial index only on a column of a spatial data type. For information about the spatial data types supported by seekdb, see Spatial data types.

  • The column attribute of the column on which you create a spatial index must be NOT NULL. If the column attribute is not NOT NULL, you can run the ALTER TABLE statement to change the column attribute to NOT NULL before you create a spatial index. For more information about how to modify the column attribute, see Define the constraint type of a column.

  • seekdb does not support the ALTER TABLE statement for modifying the SRID attribute of a column. Therefore, you must define the SRID attribute of a spatial column when you create a table to make the spatial index take effect.

• Limitations and considerations on full-text indexes

  You can create a full-text index on a non-partitioned table or a partitioned table regardless of whether the table has a primary key. The limitations on creating a full-text index are as follows:

  • A full-text index can be created only on a CHAR, VARCHAR, or TEXT column.
  • Only local (LOCAL) full-text indexes can be created in the current version.
  • The UNIQUE keyword cannot be specified when you create a full-text index.
  • If you want to create a full-text index on multiple columns, the character sets of these columns must be the same.

Recommendations for creating indexes

• We recommend that you use a concise name to describe the columns and purpose of the index, for example, idx_customer_name. For more information about naming conventions, see Overview of object naming conventions.

• If the partitioning rules and the number of partitions of a global index are the same as those of the primary table, we recommend that you create a local index.

• We recommend that you issue the CREATE INDEX statement in parallel. The number of concurrent CREATE INDEX statements must not exceed the number of CPU cores of seekdb. For example, if the seekdb specification is 4 CPU cores (4C), we recommend that you create no more than 4 indexes in parallel.

• We recommend that you create fewer indexes for tables that are frequently updated and create indexes for fields that are frequently used for queries.

• We recommend that you do not create indexes for tables with a small amount of data. If the amount of data is small, the time required to query all data may be shorter than the time required to traverse the index, and the index may not be effective.

• We recommend that you do not create indexes if the modification performance is much higher than the query performance.

• To create an efficient index:

  • The index must contain all the columns required for queries. The more columns the index contains, the fewer rows need to be traversed during a query.

  • Equality conditions must be placed at the beginning of the index.

  • Conditions that filter or sort large amounts of data must be placed at the beginning of the index.

Create an index by using a CLI

You can run the CREATE INDEX statement to create an index.

info

You can run the SHOW INDEX FROM table_name; statement to query the index information of a table, where table_name is the name of the table.

Example

Example 1: Create a unique index

If the indexed column does not contain duplicate values, you can create a unique index.

Run the following SQL statements to create a table named tbl1 and create a unique index on the col2 column of the table tbl1.

1. Create the table tbl1.

   CREATE TABLE tbl1(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));

2. Create a unique index named idx_tbl1_col2 on the col2 column of the table tbl1.

   CREATE UNIQUE INDEX idx_tbl1_col2 ON tbl1(col2);

3. View the index information of the table tbl1.

   SHOW INDEX FROM tbl1;

   The return result is as follows:

   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl1  |          0 | PRIMARY       |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl1  |          0 | idx_tbl1_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set

Example 2: Create a non-unique index

Run the following SQL statements to create a table named tbl2 and create an index on the col2 column of the table tbl2.

1. Create the table tbl2.

   CREATE TABLE tbl2(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));

2. Create an index named idx_tbl2_col2 on the col2 column of the table tbl2.

   CREATE INDEX idx_tbl2_col2 ON tbl2(col2);

3. View the index information of the table tbl2.

   SHOW INDEX FROM tbl2;

   The return result is as follows:

   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl2  |          0 | PRIMARY       |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl2  |          1 | idx_tbl2_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set

Example 3: Create a local index

A local index is also known as a partitioned index. The keyword for creating a local index is LOCAL. The partitioning key of a local index is the same as that of the table, and the number of partitions of a local index is the same as that of the table. Therefore, the partitioning mechanism of a local index is the same as that of the table. You can create a local index or a local unique index. If you want to use a local unique index to enforce data uniqueness, the local unique index must contain the table partitioning key.

Run the following SQL statements to create a table named tbl3_rl as a range-list partitioned table and create a local unique index on the col1 and col2 columns of the table tbl3_rl.

1. Create the range-list partitioned table tbl3_rl.

   CREATE TABLE tbl3_rl(col1 INT,col2 INT) 
     PARTITION BY RANGE(col1)
     SUBPARTITION BY LIST(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES IN(1,3),
         SUBPARTITION sp1 VALUES IN(4,6),
         SUBPARTITION sp2 VALUES IN(7,9)),
       PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES IN(1,3),
         SUBPARTITION sp4 VALUES IN(4,6),
         SUBPARTITION sp5 VALUES IN(7,9))
       ); 

2. Create a local unique index named idx_tbl3_rl_col1_col2 on the col1 and col2 columns of the table tbl3_rl.

   CREATE UNIQUE INDEX idx_tbl3_rl_col1_col2 ON tbl3_rl(col1,col2) LOCAL;

3. View the index information of the table tbl3_rl.

   SHOW INDEX FROM tbl3_rl;

   The return result is as follows:

   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table   | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl3_rl |          0 | idx_tbl3_rl_col1_col2 |            1 | col1        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   | tbl3_rl |          0 | idx_tbl3_rl_col1_col2 |            2 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set

Example 4: Create a global index

The keyword for creating a global index is GLOBAL.

Run the following SQL statements to create a table named tbl4_h as a hash-partitioned table and create a global index on the col2 column of the table tbl4_h.

1. Create the hash-partitioned table tbl4_h.

   CREATE TABLE tbl4_h(col1 INT PRIMARY KEY,col2 INT)
     PARTITION BY HASH(col1) PARTITIONS 5;

2. Create a global index named idx_tbl4_h_col2 on the col2 column of the table tbl4_h. The index is a range-partitioned index.

   CREATE INDEX idx_tbl4_h_col2 ON tbl4_h(col2) GLOBAL
     PARTITION BY RANGE(col2)
     (PARTITION p0 VALUES LESS THAN(100), 
       PARTITION p1 VALUES LESS THAN(200), 
       PARTITION p2 VALUES LESS THAN(300)
     );

3. View the index information of the table tbl4_h.

   SHOW INDEX FROM tbl4_h;

   The return result is as follows:

   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table  | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl4_h |          0 | PRIMARY         |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl4_h |          1 | idx_tbl4_h_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set

Example 5: Create a spatial index

A spatial index is a database index used for processing and optimizing spatial data. It is widely used in geographic information systems (GIS) and for storing and querying location data. seekdb supports creating spatial indexes by using the syntax for creating regular indexes. However, spatial indexes must be created by using the SPATIAL keyword.

Run the following SQL statements to create a table named tbl5 and create a spatial index on the g column of the table tbl5.

1. Create the table tbl5.

   CREATE TABLE tbl5(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 0);

2. Create a spatial index named idx_tbl5_g on the g column of the table tbl5.

   CREATE INDEX idx_tbl5_g ON tbl5(g);

3. View the index information of the table tbl5.

   SHOW INDEX FROM tbl5;

   The return result is as follows:

   +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl5  |          1 | idx_tbl5_g |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     | NULL       |
   +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   1 row in set

Example 6: Create a function index

An index built based on the values of one or more columns in a table is called a function index. A function index is an optimization technique. By using a function index, you can quickly locate matching function values during queries and avoid redundant calculations, thereby improving query efficiency.

In seekdb, expressions of some system functions are not supported for function indexes. For more information, see List of system functions supported for function indexes and List of system functions not supported for function indexes.

Session variable fixing mechanism

The session variable fixing mechanism in function indexes ensures that the calculation semantics of the index are consistent when the index is created and used. This prevents the scenario where an index is hit but the results are incorrect due to changes in the session environment.

Working principle

When you create a function index, the system automatically analyzes the index expression and identifies which session variables (such as time_zone, sql_mode, and collation_connection) the expression depends on. The current values of these variables are saved in the index metadata.

When a query hits a function index:

1. The system retrieves the session variable snapshot saved during index creation based on the local_session_var_id in the index expression.
2. During expression type inference and calculation, the saved variable values are used instead of the current session variable values.
3. The calculation results are ensured to be consistent with those during index creation.

Supported session variables

The session variables that can be fixed in function indexes include:

| Variable | Description | |--------|------|----------| | time_zone | The time zone setting, which affects the results of time-related functions (such as NOW() and CURTIME()). | | sql_mode | The SQL mode, which affects SQL syntax parsing and execution behavior. | | collation_connection | The collation for the connection, which affects string comparisons and function results. | | max_allowed_packet | The maximum allowed data packet size. | | ob_compatibility_version | The seekdb compatibility version number. |

The system dynamically infers the variables that need to be fixed based on the index expression. For example, if the index expression contains a time function like NOW(), the time_zone variable is fixed. If the expression contains string functions, the collation_connection variable may be fixed.

Considerations

Avoid using volatile expressions

Although the system automatically fixes session variables, it is recommended to minimize the use of functions affected by the session in indexes and prioritize deterministic columns:

-- ❌ Not recommended (depends on time_zone)
CREATE INDEX idx_now ON logs ((DATE(NOW())));

-- ✅ Recommended (uses a deterministic column)
CREATE INDEX idx_log_date ON logs ((DATE(log_time)));

No need to manually manage session variables

The system automatically handles session variable consistency. You do not need to worry about changes in the current session variables affecting the correctness of existing function indexes. Queries will automatically use the variable snapshots saved during index creation.

Use the following SQL statement to create a table named tbl6 and create a function index based on the c_time column of the table.

1. Create the tbl6 table.

   CREATE TABLE tbl6(id INT, name VARCHAR(18), c_time DATE);

2. Create an index named idx_tbl6_c_time on the c_time column of the tbl6 table.

   CREATE INDEX idx_tbl6_c_time ON tbl6((YEAR(c_time)));

3. Use the following SQL statement to view the created function index.

   SHOW INDEX FROM tbl6;

   The returned result is as follows:

   SHOW INDEX FROM tbl6;
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   | Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression     |
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   | tbl6  |          1 | idx_tbl6_c_time |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | year(`c_time`) |
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   1 row in set

Example 7: Create a columnstore index

A columnstore index is an index structure designed for columnar databases. It is primarily used to optimize query performance in columnar storage.

Use the following SQL statement to create a columnstore index.

After creating the tbl7 table, create a columnstore index named idx1_tbl7_cg.

1. Create the tbl7 table.

   CREATE TABLE tbl7(id INT, name VARCHAR(20), age INT);

2. Create a columnstore index named idx1_tbl7_cg on the tbl7 table and store redundant data of the age column in the index table.

   CREATE INDEX idx1_tbl7_cg ON tbl7(id) STORING(age) WITH COLUMN GROUP(each column);

3. Use the following SQL statement to view the created function index.

   SHOW INDEX FROM tbl7;

   The returned result is as follows:

   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl7  |          1 | idx1_tbl7_cg |            1 | id          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   1 row in set

Example 9: Create a full-text index

Here is an example:

After creating the tbl8 table, create a full-text index named ft_idx1_tbl8.

1. Create the tbl8 table.

   CREATE TABLE tbl8(col1 INT, col2 VARCHAR(4096));

2. Create a full-text index named ft_idx1_tbl8 on the tbl8 table, specify the IK tokenizer for the full-text index, and set tokenizer properties through PARSER_PROPERTIES.

   CREATE FULLTEXT INDEX ft_idx1_tbl8 ON tbl8(col2)
       WITH PARSER IK
       PARSER_PROPERTIES=(ik_mode='max_word');

3. Use the following SQL statement to view the created function index.

   SHOW INDEX FROM tbl8;

   The returned result is as follows:

   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl8  |          1 | ft_idx1_tbl8 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | FULLTEXT   | available |               | YES     | NULL       |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   1 row in set

What to do next

After you create an index, you may need to optimize query performance.