Summary of cases where MySQL indexes will not be used

Types of indexes in MySQL

1 can be divided into 4 categories:

Common index: The most common index 1-only index: The value of the index column must be 1-only, but null values are allowed Primary key index: A special 1-only index, null values are not allowed Joint index: The index column has multiple fields, and the leftmost prefix principle needs to be met when using it

General index

This is the most basic index, and it has no restrictions. It can be created in the following ways:

1. Create an index

The code is as follows:

CREATE INDEX indexName ON mytable(username(length));

If it is CHAR, VARCHAR type, length can be less than the actual length of the field; For BLOB and TEXT types, length must be specified, the same below.

2. Modify the table structure

The code is as follows:

ALTER mytable ADD INDEX [indexName] ON (username(length))

Directly specify when creating a table

CREATE TABLE mytable(
 ID INT NOT NULL,
 username VARCHAR(16) NOT NULL,
 INDEX [indexName] (username(length))
);

Syntax for deleting indexes:

DROP INDEX [indexName] ON mytable;

Only 1 index

It is similar to the previous ordinary index, except that the value of the index column must be only 1, but null values are allowed. If it is a composite index, the combination of column values must be only 1. It can be created in the following ways:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

Modify the table structure:

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

When creating a table, specify directly:

CREATE TABLE mytable(  
	ID INT NOT NULL,  
	username VARCHAR(16) NOT NULL,  
	UNIQUE [indexName] (username(length))  
);

Primary key index

It is a special 1-only index, and null values are not allowed. 1 is to create the primary key index at the same time when building a table:

The code is as follows:

CREATE TABLE mytable(  
	ID INT NOT NULL,  
	username VARCHAR(16) NOT NULL,  
	PRIMARY KEY(ID) 
);

Of course, you can also use the ALTER command. Remember: A table can only have one primary key.

Union index

To visually compare single-column indexes with composite indexes, add multiple fields to the table:

CREATE TABLE mytable(  
	ID INT NOT NULL,  
	username VARCHAR(16) NOT NULL,  
	city VARCHAR(50) NOT NULL,  
	age INT NOT NULL 
);

In order to extract the efficiency of MySQL in one step, it is necessary to consider establishing a composite index. Is to build name, city and age into one index:

The code is as follows:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

The situation that the index is not used

Indexes don't take effect every time. If we do not operate correctly, it is very likely that we will scan the whole table instead of the index. The parameters possible_key, key_len and key in Explain can analyze whether our SQL statement uses indexes.

The following conditions will cause the index to fail

Is used in the column of the query! = For example, select id, name, age from student where id! = 2; Functional operations are used in the columns of the query, such as the function expression pow (id, 2) squares id, and no index is used If there is or in the condition, even if some of the conditions are indexed, they will not be used. (This is why or is used as little as possible.) Even if the leftmost prefix principle is satisfied in the union index, but the first condition has a range query, then the index will not be used If there is an invisible data type conversion for indexed columns, the index is not used. For example, if the column type is a string, the data must be quoted in quotation marks in the condition, otherwise the index is not used If MySQL estimates that using full table scanning is faster than using indexes, no indexes are used

All of the above can be tested using Explain for indexing.

Summarize