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).

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)