MySQL Composite Index

One

1. The fewer indexes, the better, when the data is modified, the first index is updated to reduce the write speed.
2. The narrowest field is placed on the left side of the key
3. Avoid file sort sorting, temporary tables, and table scans.

Second, the establishment of composite Index principles:

If you are likely to perform a search more than once on only one column, the column should be the first column in the composite Index. If you are likely to perform a separate search for two columns in a two-column index, you should create another index that contains only the second column.
As shown, if you need to query for age and gender in your query, you should create a new composite index that contains age and gender.
A primary key that contains multiple columns is always automatically created as a composite index, in the order in which they appear in the table definition, rather than in the order specified in the primary key definition. Determine which column should be at the top, considering the search that will be performed by the primary key in the future.
Note that creating a composite index should contain a few columns, and these columns are often used in select queries. Including too many columns in a composite index does not give you too much benefit. And because of the amount of memory used to store the values of the columns of the composite index, the consequence is memory overflow and performance degradation.


If the query condition of a like statement does not start with a wildcard character, the index is used.
such as:% Car or% car% do not use index.
Vehicle% use index.

MySQL Composite Index