How to optimize mysql Indexes

How to optimize mysql indexes? This may be a problem that many people need. The following describes an example of mysql index optimization for your reference.

Example of mysql index optimization:

A large number of locks occur after a SQL statement is run, and many other queries are blocked. The index structure of the current table.
This problem was found by using show processlist and slow query log.
Mysql> show index from t_slide;
+ --------- + ------------ + ----------------------- + -------------- + ------------- +
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+ --------- + ------------ + ----------------------- + -------------- + ------------- +
| T_slide | 0 | PRIMARY | 1 | Fslide_id |
| T_slide | 1 | Fuser_id | 1 | Fuser_id |
| T_slide | 1 | Fcategory | 1 | Fcategory |
| T_slide | 1 | Fdomain_key | 1 | Fdomain_key |
| T_slide | 1 | idx_t_slide_fadd_time | 1 | Fadd_time |

Executed SQL statement:
SELECT * FROM t_slide WHERE Fuser_id = 200468950 and (Fstatus = 1 or Fstatus = 4) and fmit _set <> 9 order by Fadd_time desc limit;

This user has more than rows of data and uses explain to analyze the SQL Execution Process, which takes 8 seconds. The index used is idx_t_slide_fadd_time.
Then, the system first searches for data in a global order.

After analysis, the index Fuser_id is forcibly used, so that MYSQL can locate the user for processing first. Use index to forcibly use Indexes
SELECT * FROM t_slide use index (Fuser_id) WHERE Fuser_id = 200468950 and (Fstatus = 1 or Fstatus = 4) and Fshare_set <> 9 order by Fadd_time desc limit;

The speed is increased to 2 seconds, although it is still slow, but it is not so serious.

Mysql index optimization if the Fuser_id and Fadad_time fields are put together using the combined index for indexing. The speed is extremely fast and the improvement effect is obvious.
Alter table t_slide add index SLIDE_TIME (Fslide_id, Fadd_time );

MySQL index usage

MySql connection string description

Three statuses of mysql triggers

Create MySQL trigger syntax

Three examples of mysql triggers