Skip to content

Create secondary index

What is secondary index

Indexes identified on non-clustered keys, secondary indexes are also called non-clustered indexes, which are used to improve query performance and accelerate data retrieval. The secondary index does not directly store table data, but indexes a portion of the data (such as a column), allowing the database system to quickly locate rows containing specific values ​​in the table.

Using secondary indexes can help speed up query operations, especially when querying large tables. Secondary indexes can also be used to support sorting, grouping, and join operations that usually require sorting or matching a portion of the data in the table.

Prepare before starting

Before reading this page, you need to prepare the following:

Use secondary indexing

Create a secondary index You can create a secondary index through the CREATE INDEX statement, specifying the columns to which the index is targeted, and other index options.

The syntax structure is: CREATE INDEX index_name ON table_name (column_name);

Where index_name is the name of the index, table_name is the name of the table on which to create the index, and column_name is the name of the column used to create the index.

For example, if you want to create a secondary index on the last_name column of a table named employees, you can use the following SQL statement:

CREATE INDEX idx_lastname ON employees (last_name);

Use secondary indexes: You can use secondary indexes in a query statement to locate data rows. The SQL query optimizer automatically selects the appropriate index to perform query operations for optimal performance. like:

SELECT * FROM employees WHERE last_name = 'Smith';

In this example, the query optimizer will use the idx_lastname index to locate the data rows with last_name as Smith.

It should be noted that creating indexes increases the storage and maintenance costs of the database and may also affect performance when inserting, updating, and deleting data. Therefore, when creating secondary indexes, it is necessary to carefully consider their impact on database performance and make necessary optimizations and tunings.

Example

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  email VARCHAR(50)
);
-- We can create a secondary index on the table to speed up querying users by name
CREATE INDEX idx_users_name ON users(name);
-- Insert some data
INSERT INTO users VALUES (1, 'John', 30, 'john@gmail.com');
INSERT INTO users VALUES (2, 'Tommy', 50, 'tom@gmail.com');
INSERT INTO users VALUES (3, 'Ann', 33, 'ann@gmail.com');
-- Perform the following query, the database can use secondary indexes to quickly find all users named "John" without having to scan the entire table.
mysql> SELECT * FROM users WHERE name = 'John';
+------+-------+-------------------------------------------+
| id | name | age | email |
+------+-------+-------------------------------------------+
| 1 | John | 30 | john@gmail.com |
+------+-------+-------------------------------------------+
1 row in set (0.00 sec)

limit

Currently, MatrixOne Intelligence only implements secondary indexing syntax and does not achieve performance improvements.