Indexing problems in MySQL

Purpose of the Index

Increasing the efficiency of queries is equivalent to creating an alphabet or a radical table in a dictionary, so that the query is certainly much faster than a row of queries

The length of each storage engine can be indexed is not the same, but each table supports at least 16 indexes, with a total index length of at least 256 bytes. Most storage engines have a higher limit.

To create a delete index

CREATE INDEX index_name [USING index--type] on table_name (INDEX_COL_NAME)

Including: index_col_name:col_name[(length)] [ASC | DESC]

Delete: Drop index index_name on Tabel_name

Categories of indexes:

The default for the Mysiam and InnoDB storage engines is the Btee index.

While memory storage engine supports hash index, the problem of hash index is: only use = or

The <=> operator comparison, used for order by cannot use the index.

To view the usage of the indexmysql> Show status like ' handler_read% ';

Where Handler_read_key represents the number of times a row is read by the index value, a low value indicates that the performance improvement resulting from the index is not improved because the index is not used frequently. Handler_read_rnd_next represents the number of requests to read the next line in the data file, if a large number of scan tables, this value will be very high, the index should be indexed, or the current index is unreasonable, need to re-establish the index.

+-----------------------+-------+

| variable_name | Value |

+-----------------------+-------+

| Handler_read_first | 1 |

| Handler_read_key | 141 |

| Handler_read_next | 12 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 24 |

| Handler_read_rnd_next | 495 |

Principles for designing indexes:

The index column of the search is not necessarily the column you want to select, that is, the column that is most appropriate for the index is the column that appears in the WHERE clause, or the column specified in the JOIN clause, rather than the column that appears in the selection list after the Select keyword.

Use a unique index. The larger the cardinality of the indexed columns, the better the indexes will be if there are more columns.

Use a short index, prefix index. For large strings, just specify the preceding characters to make the index, because the index also requires disk space.

Using the leftmost index is the column that is written at the first position when the index is built.

Do not overuse indexes: the build of an index takes up additional disk space and reduces the performance of write operations. When you modify the use of tables, the indexes must also be updated. As long as the desired index is maintained to facilitate query optimization

Note the point:

When a composite index is created, the index is used to the leftmost index column in the general query condition.

When you use the LIKE keyword in a query condition, the% cannot be placed at the beginning, otherwise the index will not be used.

When you use the OR keyword in a query condition, you should have a separate index for each query condition

Indexing problems in MySQL