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

(1) Log on to MySQL database
(2) Create database index_test
(3) Create table test_table1
(4) Create table test_table2 with storage engine MyISAM
(5) Using alter table statement to create a common index named ComDateIdx on the birth field of table test_table2
(6) Use alter table statement to add a unique index named UniqIdx2 to the id field of table test_table2 and arrange it in descending order.
(7) Use create index to create a composite index named MultiColidx2 on three fields: first name, middlename and last name
(8) Create a full-text index named FTidx on the title field using create index
(9) Use the alter table statement to delete the unique index named Uniqidx in table test_table1
(10) Use the drop index statement to delete the combined index named MultiColidx2 in table test_table2
Several Points for Attention

(1) Log on to 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_table1
Create 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_ci
1 row in set (0.06 sec)

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

(4) Create table test_table2 with storage engine 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) Using 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 alter table statement to add a unique index named Uniqidx2 to 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 name on the first name and middlename fields Composite Index of MultiColidx2

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

(8) Create a full-text index named FTidx on the title field using create index

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 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 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 for attention:

1. Index is so important to database performance, how to use it?

• If there are fewer index columns, less disk space and maintenance overhead are required.
• If multiple composite indexes are created on a large table, the index files will also expand rapidly. In addition, more indexes can cover more queries.
• Attempts to add, delete, and modify indexes do not affect database architecture or application design.

2. Use short indexes as much as possible

• Index fields of string type and specify a prefix length if possible. For example, there is a char(255) column that does not need to be indexed if most values are unique in the first 10 or 30 characters.
• Short index can not only improve query speed, but also save disk space and reduce I/O operations.