Summary of causes of index failure in Mysql and summary of index failure in mysql

Summary of causes of index failure in Mysql and summary of index failure in mysql

Http://blog.csdn.net/hehexiaoxia/article/details/54312130

When performing queries in a database, you often find that the query is slow, but you have already created an index on the column. After research, we found that the index fails in many cases.

The following is a summary of the causes of index failure (excluding the case where the index itself is invalid). You are welcome to add this article.

1. An index is created for a single field. The where condition contains multiple fields.

For example, the following indexes are created:

Query statement:

[Html]View plain copy

1. Select * from template t where t. logicdb_id = 4 and t. sync_status = 1

2. Create a joint index. The where condition is a single field. This is the opposite of the above case.

For example, the following indexes are created:

Query statement:

[Html]View plain copy

1. Select * from template t where t. sync_status = 4

3. Index column operations, including (+ ,-,*,/,! , <>, %, Like '% _' (% placed before), or, in, exist, etc.), leading to index failure.

4. Type Error. For example, if the field type is varchar, the where condition uses number.

For example, the template_id field is of the varchar type.

Incorrect syntax: select * from template t where t. template_id = 1

Statement: select * from template t where t. template_id = '1'

5. apply internal functions to the index. In this case, create a function-based index.

Example:

[Html]View plain copy

1. Select * from template t where ROUND (t. logicdb_id) = 1

In this case, set ROUND (t. logicdb_id) as the index.

6. The efficiency of querying a table is faster than that of applying an index.

7. The is null index is invalid. The is not null Betree index takes effect. The cause is that mysql is not written into the index in null. Depends on the application database.

View comments