Detailed explanation of how MySQL creates indexes (case)

Case: 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 data table according to the operation process basic operations.

(1) Log in to the MySQL database
(2) Create the database index_test
(3) Create the table test_table1
(4) Create the table test_table2, the storage engine is MyISAM
(5) Use the alter table statement to create a common index named ComDateIdx on the birth field of the table test_table2
(6) Use the alter table statement to add a unique index named UniqIdx2 on the id field of the table test_table2 and arrange it in descending order
(7) Use create index to create a combined index named MultiColidx2 on the firstname, middlename and lastname fields
(8) Use create index to create a full-text index named FTidx on the title field
(9) Use The alter table statement deletes the unique index named Uniqidx in the table test_table1
(10) Use the drop index statement to delete the combined index named MultiColidx2 in the table test_table2
A few points to note

( Free learning recommendations: mysql video tutorial)

---

(1) Log in to the MySQL database

C:\Users\Hudie>mysql -h localhost -u root -p
Enter password: *******

(2) Create database index_test

mysql> create database index_test;Query OK, 1 row affected (0.06 sec)mysql> use index_test;Database changed

(3) Create table test_table1

mysql> 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 \G*************************** 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,
  `description` char(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqidx` (`id`),
  KEY `MultiColidx` (`name`(20),`address`(30)),
  KEY `Comidx` (`description`(30))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)

You can see that 3 indexes were successfully created in the test_table table, each with the name uniqidx on the id field A unique index on the name and address fields; a normal index of length 30 on the description field.

(4) Create table test_table2, the storage engine is MyISAM

mysql> create table test_table2    -> (
    -> id int not null primary key auto_increment,
    -> firstname char(100) not null,
    -> middlename char(100) not null,
    -> lastname char(100) not null,
    -> birth date not null,
    -> title char(100) null
    -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)

(5) Use the alter table statement to create a common 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 named Uniqidx2 on the id field of table test_table2

mysql> alter table test_table2 add unique index Uniqidx(id);Query OK, 0 rows affected (0.11 sec)Records: 0  Duplicates: 0  Warnings: 0

(7) Use create index to create a combination named MultiColidx2 on the firstname and middlename fields. Index

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 create a full-text index named FTidx on the title field

mysql> create fulltext index ftidx on test_table2(title);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 named Uniqidx in the table test_table1

mysql> alter table test_table1 drop index uniqidx;Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0

(10) Use the drop index statement to delete the combined index named MultiColidx2 in the table test_table2

mysql> drop index MultiColidx2 on test_table2;Query OK, 0 rows affected (0.12 sec)Records: 0  Duplicates: 0  Warnings: 0

Several points to note:

1. Indexes are so important to the performance of the database , how to use it?

• If there are fewer index columns, less disk space and maintenance overhead are required.
• If multiple combination indexes are created on a large table, the index file will also expand quickly. In addition, there are more indexes, which can cover more queries.
• Attempt to add, delete, or modify indexes without affecting the database schema or application design.

2. Try to use short indexes

• to index string type fields. If possible, you should specify a prefix length. For example, if you have a char(255) column, if most values ​​are unique within the first 10 or 30 characters, there is no need to index the entire column.
• Short indexes can not only improve query speed, but also save disk space and reduce I/O operations.

Related free learning recommendations: mysql database(Video)

The above is the detailed content of Detailed explanation of how MySQL creates indexes (case). For more information, please follow other related articles on the PHP Chinese website!