MySQL adds indexes and slows down
Several situations of index failure
1. Using in and not in will cause the index to fail.
- The reason for the failure is related to the version of Mysql and the amount of data in the table.
- Versions after 8.0 are indexed
Conditions for segmentation
If the condition on the left or the condition on the left has an index, and the condition on the right has no index, it will not go through the index
- Because the meaning of OR is that two are satisfied with one
- Therefore, it does not make sense to index only one conditional column
- As long as the conditional column is not indexed
- A full table scan will be performed
- Therefore, the indexed conditional column will also fail
3. The index field is not the leftmost field of the joint index field
'%' fuzzy match was used
5. Built-in functions are used in the indexed field
- SUM(), MIN(), MAX(), COUNT(), and so forth
6. The indexed field uses expression calculation
7. Index failure caused by field type mismatch
mysql add index statement
Add PRIMARY KEY (primary key index)
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
Add UNIQUE (unique index)
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )
Add INDEX (normal index)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
Add FULLTEXT (full text index)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
Add multiple column index
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.