CREATE INDEX

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.

Syntax

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

Explanations

CREATE UNIQUE INDEX

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

Examples

  1. drop table if exists t1;
  2. create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
  3. insert into t1 values(1,"Abby", 24);
  4. insert into t1 values(2,"Bob", 25);
  5. insert into t1 values(3,"Carol", 23);
  6. insert into t1 values(4,"Dora", 29);
  7. create unique index idx on t1(name);
  8. mysql> select * from t1;
  9. +------+-------+------+
  10. | id   | name  | age  |
  11. +------+-------+------+
  12. |    1 | Abby  |   24 |
  13. |    2 | Bob   |   25 |
  14. |    3 | Carol |   23 |
  15. |    4 | Dora  |   29 |
  16. +------+-------+------+
  17. 4 rows in set (0.00 sec)
  19. mysql> show create table t1;
  20. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
  21. | Table | Create Table                                                                                                                                     |
  22. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
  23. | t1    | CREATE TABLE `t1` (
  24. `id` INT NOT NULL,
  25. `name` VARCHAR(255) DEFAULT NULL,
  26. `age` INT DEFAULT NULL,
  27. PRIMARY KEY (`id`),
  28. UNIQUE KEY `idx` (`name`)
  29. ) |
  30. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
  31. 1 row in set (0.01 sec)
  34. create table t2 (
  35. col1 bigint primary key,
  36. col2 varchar(25),
  37. col3 float,
  38. col4 varchar(50)
  39. );
  40. create unique index idx on t2(col2) comment 'create varchar index';
  41. insert into t2 values(1,"Abby", 24,'zbcvdf');
  42. insert into t2 values(2,"Bob", 25,'zbcvdf');
  43. insert into t2 values(3,"Carol", 23,'zbcvdf');
  44. insert into t2 values(4,"Dora", 29,'zbcvdf');
  45. mysql> select * from t2;
  46. +------+-------+------+--------+
  47. | col1 | col2  | col3 | col4   |
  48. +------+-------+------+--------+
  49. |    1 | Abby  |   24 | zbcvdf |
  50. |    2 | Bob   |   25 | zbcvdf |
  51. |    3 | Carol |   23 | zbcvdf |
  52. |    4 | Dora  |   29 | zbcvdf |
  53. +------+-------+------+--------+
  54. 4 rows in set (0.00 sec)
  55. mysql> show create table t2;
  56. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  57. | Table | Create Table                                                                                                                                                                                                              |
  58. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  59. | t2    | CREATE TABLE `t2` (
  60. `col1` BIGINT NOT NULL,
  61. `col2` VARCHAR(25) DEFAULT NULL,
  62. `col3` FLOAT DEFAULT NULL,
  63. `col4` VARCHAR(50) DEFAULT NULL,
  64. PRIMARY KEY (`col1`),
  65. UNIQUE KEY `idx` (`col2`) COMMENT `create varchar index`
  66. ) |
  67. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  68. 1 row in set (0.01 sec)