Mysql index selection

Welcome to the Linux community forum and interact with 2 million technical staff. 1. Try to index the data columns used for search, classification, or grouping. Do not index the data columns displayed as output. The most suitable data columns with indexes are the data columns in the where clause, the data columns that appear in the join clause, or in the Groupby, Orderby

Welcome to the Linux community forum and interact with 2 million technical staff> 1. Try to index the data columns used for search, classification, or grouping, do not index the data columns displayed as output. The most suitable data columns with indexes are the data columns in the where clause, the data columns that appear in the join clause, or in Group by, Order

Welcome to the Linux community forum and interact with 2 million technicians>

1. Try to index the data columns used for search, classification, or grouping. Do not index the data columns displayed as output.

The data columns with indexes are the data columns in the where clause, the data columns in the join clause, or the data columns in the Group by and Order by clauses. It is best not to use indexes for data columns after select.

2. comprehensively consider the dimensions of each data column.

The dimension of a data column is equal to the number of non-repeated values it contains. For example, if the value of a data column is 1, 2, 3, 3, and 4, the dimension is 3. The higher the dimension, the more suitable it is for indexing. The reason is self-evident.

3. Index short values.

4. Create an index for the prefix of a string.

5. Make full use of the leftmost prefix.

When you create an index that matches n data columns, because the leftmost data column set in the index can use n indexes. A composite index is equivalent to n indexes at work, because the leftmost data column set of the index can be used to match data rows. Such a set is equivalent to the "leftmost prefix". MYSQL cannot use an index without the leftmost prefix.

6. Do not create too many indexes.

Indexes occupy additional disk space and affect write performance. When you have too many indexes, mysql cannot even choose the best index to use.

7. Make the index type match the type of the comparison operation you intend to perform. (Use the keyword using btree for B-tree Indexing ).

8. Use the slow query log to find the query with poor performance. (The Slow query log file is a text file .)