MySQL Index Creation Rules

The rules for creating an index are as follows:

1. Uniqueness: The values of an indexed column should be unique, ensuring data integrity through the use of indexes.
2. Selectivity: The values of the indexed column should have high selectivity, meaning the more different values, the better. The higher the selectivity, the better the index will be.
3. Length: The length of the index column should be as small as possible in order to reduce the storage space occupied by the index and improve query performance.
4. Choosing the appropriate data type can reduce the size of the index and improve query performance.
5. Prefix indexing allows for only a portion of a longer string to be indexed, reducing the storage space needed for the index.
6. Do not create too many indexes: creating too many indexes can increase the maintenance cost of the database and may lead to a decrease in performance.
7. Composite index: Create a composite index for multiple columns that are frequently used together to improve query performance.
8. Avoid using the not equal to (<>) operator in your queries as it cannot utilize indexes, resulting in a full table scan and degrading query performance.
9. Columns that are frequently updated are not suitable for indexing as the maintenance cost of the index increases, leading to a decrease in database performance.
10. Columns used in a query are suitable for indexing: Columns frequently used in queries should be indexed to improve query speed.