If there is too much data in the table, it will affect the efficiency of the query, then we need to find a way to optimize the query, usually adding an index is one of our choices;
1. Add primary key (primary key index)
Mysql>alter TABLE ' table_name ' ADD PRIMARY KEY (' column ')
2. Add unique (unique index)
Mysql>alter TABLE ' table_name ' ADD UNIQUE (
' Column '
)
3. Add index (normal index)
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column ')
4. Add fulltext (full-text index)
Mysql>alter TABLE ' table_name ' ADD fulltext (' column ')
5. Adding Multi-column indexes
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column1 ', ' column2 ', ' column3 ')
For example:
Let's try adding an index to the T_name field in test.
Mysql> ALTER TABLE test add index (T_NAME);
Query OK, 0 rows affected (0.17 sec)
records:0 duplicates:0 warnings:0
After successful execution, let's take a look at the results.
mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_id | Int (11) | YES | | NULL | |
| T_name | varchar (50) | NO | MUL | NULL | |
| T_password | char (32) | YES | | NULL | |
| T_birth | Date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in Set (0.00 sec)
MySQL database add index to optimize query efficiency