CREATE INDEX¶

Create indexes on tables to query data more quickly and efficiently.

Description¶

Create indexes on tables to query data more quickly and efficiently.

You can’t see the index; the index can only be used to speed up the search/query.

Updating a table with an index takes longer than updating a table without an index because the index also needs to be updated. Therefore, the ideal approach is to create indexes only on frequently searched columns (and tables).

There are two common types of indexes, namely:

Primary Key: The primary key index, that is, the index identified on the primary key column.
Secondary Index: the secondary index, that is, the index identified on the non-primary key. Secondary indexes participate in query optimization; EXPLAIN shows Index Table Scan for queries that match indexed columns.

Syntax¶

> CREATE [UNIQUE] INDEX index_name
ON tbl_name (key_part,...)
COMMENT 'string'

Explanations¶

CREATE UNIQUE INDEX¶

Creates a unique index on a table. Duplicate values are not allowed.

Prefix Indexes on VARCHAR Columns¶

MatrixOne supports prefix indexes on VARCHAR columns. A prefix index stores and indexes only the first N characters (or bytes) of the column value, which can reduce index size for long string columns while still supporting efficient lookups.

Syntax:

INDEX index_name (col_name(N))
UNIQUE INDEX index_name (col_name(N))

Where N is the prefix length, specifying how many characters of the column value to include in the index.

Supported forms:

Inline in CREATE TABLE: CREATE TABLE t(a VARCHAR(32), INDEX idx_a(a(4)))
Standalone CREATE INDEX: CREATE INDEX idx_a ON t(a(4))
ALTER TABLE ... ADD INDEX: ALTER TABLE t ADD INDEX idx_a(a(4))

Key behaviors:

For UNIQUE prefix indexes, uniqueness is enforced on the prefixed portion. For example, UNIQUE INDEX uq_a(a(4)) prevents duplicate values in the first 4 characters of column a.
Prefix indexes support backfill: you can insert data into the table first, then create the prefix index. The existing data will be indexed.
Prefix indexes work with all DML operations (INSERT, UPDATE, DELETE) and are used by the optimizer for query acceleration.

Examples:

DROP TABLE IF EXISTS prefix_demo;
CREATE TABLE prefix_demo (
    id INT PRIMARY KEY,
    name VARCHAR(32),
    description VARCHAR(32),
    INDEX idx_name(name(4))
);

INSERT INTO prefix_demo VALUES (1, 'abcdef-long', 'blue');
INSERT INTO prefix_demo VALUES (2, 'uvwxyz-long', 'green');

-- Query using the prefix index
SELECT id, name, description FROM prefix_demo WHERE name = 'uvwxyz-long';

-- Standalone CREATE INDEX with prefix
CREATE TABLE prefix_demo2 (id INT PRIMARY KEY, name VARCHAR(32), val INT);
INSERT INTO prefix_demo2 VALUES (1, 'abcdef-long', 10), (2, 'lmnopq-long', 20);
CREATE INDEX idx_name2 ON prefix_demo2(name(4));

-- ALTER TABLE ADD INDEX with prefix
CREATE TABLE prefix_demo3 (id INT PRIMARY KEY, name VARCHAR(32), val INT);
INSERT INTO prefix_demo3 VALUES (1, 'abcdef-long', 10), (2, 'lmnopq-long', 20);
ALTER TABLE prefix_demo3 ADD INDEX idx_name3(name(4));

-- UNIQUE prefix index: uniqueness enforced on the first 4 characters
CREATE TABLE prefix_demo4 (
    id INT PRIMARY KEY,
    name VARCHAR(32),
    val INT,
    UNIQUE INDEX uq_name(name(4))
);

INSERT INTO prefix_demo4 VALUES (1, 'abcdef-long', 10);
-- This insert would fail because 'abcd' conflicts with the prefix of 'abcdef-long'
-- Expected-Success: false
-- INSERT INTO prefix_demo4 VALUES (2, 'abcdzz-conflict', 20);

DROP TABLE prefix_demo;
DROP TABLE prefix_demo2;
DROP TABLE prefix_demo3;
DROP TABLE prefix_demo4;

Examples¶

drop table if exists t1;
create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
insert into t1 values(1,"Abby", 24);
insert into t1 values(2,"Bob", 25);
insert into t1 values(3,"Carol", 23);
insert into t1 values(4,"Dora", 29);
create unique index idx on t1(name);
mysql> select * from t1;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | Abby  |   24 |
|    2 | Bob   |   25 |
|    3 | Carol |   23 |
|    4 | Dora  |   29 |
+------+-------+------+
4 rows in set (0.00 sec)

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
`id` INT NOT NULL,
`name` VARCHAR(255) DEFAULT NULL,
`age` INT DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx` (`name`)
) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


create table t2 (
col1 bigint primary key,
col2 varchar(25),
col3 float,
col4 varchar(50)
);
create unique index idx on t2(col2) comment 'create varchar index';
insert into t2 values(1,"Abby", 24,'zbcvdf');
insert into t2 values(2,"Bob", 25,'zbcvdf');
insert into t2 values(3,"Carol", 23,'zbcvdf');
insert into t2 values(4,"Dora", 29,'zbcvdf');
mysql> select * from t2;
+------+-------+------+--------+
| col1 | col2  | col3 | col4   |
+------+-------+------+--------+
|    1 | Abby  |   24 | zbcvdf |
|    2 | Bob   |   25 | zbcvdf |
|    3 | Carol |   23 | zbcvdf |
|    4 | Dora  |   29 | zbcvdf |
+------+-------+------+--------+
4 rows in set (0.00 sec)
mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
`col1` BIGINT NOT NULL,
`col2` VARCHAR(25) DEFAULT NULL,
`col3` FLOAT DEFAULT NULL,
`col4` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`col1`),
UNIQUE KEY `idx` (`col2`) COMMENT `create varchar index`
) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)