Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to create an index in mysql database, which is very detailed and has certain reference value. Friends who are interested must finish reading it.

Example: create the database index_test, create two data tables test_table1 and test_table2 in the index_test database according to the structure of the following table, and complete the basic operation of the data table according to the operation procedure.

(1) Log in to MySQL database

(2) create database index_test

(3) create table test_table1

(4) create table test_table2, and the storage engine is MyISAM

(5) use the alter table statement to create a general index named ComDateIdx on the birth field of table test_table2

(6) use the alter table statement to add a unique index named UniqIdx2 on the id field of table test_table2, and sort it in descending order

(7) use create index to build a combined index named MultiColidx2 on three fields: firstname, middlename and lastname.

(8) use create index to build a full-text index named FTidx on the title field

(9) use the alter table statement to delete the unique index named Uniqidx in the table test_table1

(10) use the drop index statement to delete the composite index named MultiColidx2 in table test_table2

A few points of attention

(1) Log in to MySQL database C:\ Users\ Hudie > mysql-h localhost-u root-pEnter password: * (2) create database index_testmysql > create database index_test;Query OK, 1 row affected (0.06 sec) mysql > use index_test Database changed (3) create tables test_table1mysql > create table test_table1-> (- > id int not null primary key auto_increment,-> name char (100) not null,-> address char (100) not null,-> description char (100) not null,-> unique index uniqidx (id),-> index MultiColidx (name (20), address (30)),-> index Comidx (description (30)->) Query OK, 0 rows affected (0.11 sec) mysql > show create table test_table1\ Graph * 1. Row * * Table: test_table1Create Table: CREATE TABLE `test_ table1` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` char (100) NOT NULL, `address` char (100) NOT NULL `Secrettion`Colidx` NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqidx` (`id`), KEY `MultiColidx` (`name` (20), `address` (30)), KEY `Comidx` (`promotion` (30)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)

You can see that three indexes have been successfully created in the test_ table table: a unique index named uniqidx on the id field, a combined index on the name and address fields, and a normal index of length 30 on the description field.

(4) create table test_table2. The storage engine is MyISAMmysql > create table test_table2-> (- > id int not null primary key auto_increment,-> firstname char (100th) not null,-> middlename char (100th) not null,-> lastname char (100th) not null,-> birth date not null,-> title char (100null->) ENGINE=MyISAM Query OK, 0 rows affected (0.07 sec) (5) use the alter table statement to create a normal index named ComDateIdx on the birth field of table test_table2 mysql > alter table test_table2 add index ComDateidx (birth); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 (6) use the alter table statement to add a unique index mysql > alter table test_table2 add unique index Uniqidx (id) named Uniqidx2 on the id field of table test_table2 Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 (7) use create index to build a combined index named MultiColidx2 on firstname and middlename fields mysql > create index MultiColidx2 on test_table2 (firstname,middlename); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 (8) use create index to build a full-text index named FTidx mysql > create fulltext index ftidx on test_table2 (title) on the title field Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 (9) use the alter table statement to delete the unique index mysql > alter table test_table1 drop index uniqidx;Query OK named Uniqidx in table test_table1, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 (10) use the drop index statement to delete the combined index mysql > drop index MultiColidx2 on test_table2 named MultiColidx2 in table test_table2 Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 points for attention:

1. The index is so important to the performance of the database, how can I use it?

If there are fewer index columns, less disk space and maintenance overhead are required.

If multiple combined indexes are created on a large table, the index file will also swell quickly. In addition, there are more indexes, which can cover more queries.

Try to add, delete, or modify indexes without affecting the database schema or application design.

two。 Try to use short indexes

Index fields of string type and specify a prefix length if possible. For example, if you have a char column, if most of the values are unique in the first 10 or 30 characters, you don't need to index the entire column.

The short index can not only improve the query speed, but also save disk space and reduce the Icano operation.

The above is all the contents of the article "how to create an index in mysql database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.