Summary of situations where MySQL indexes will not be used

Types of Indexes in MySQL

Generally, they can be divided into four categories:

• Normal index: the most common index
• Unique index: The value of the index column must be unique, but null values ​​are allowed.
• Primary key index: a special unique index that does not allow null values
• Joint index: The index column has multiple fields, and the leftmost prefix principle must be met when using it

Normal 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 or VARCHAR type, length can be smaller than the actual length of the field; if it is BLOB or TEXT type, length must be specified, and the same applies below.

2. Modify the table structure

The code is as follows:

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

Specify directly when creating a table

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

The syntax for deleting an index is:

DROP INDEX [indexName] ON mytable;

Unique Index

It is similar to the previous ordinary index, except that the values ​​of the index column must be unique, but null values ​​are allowed. If it is a composite index, the combination of column values ​​must be unique. 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 unique index that does not allow null values. Generally, the primary key index is created when the table is created:

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.

Joint Index

To visually compare single-column indexes and 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 further squeeze the efficiency of MySQL, we should consider creating a composite index. That is, 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);

Situations where indexes are not used

Indexes may not be effective every time. If we do not operate correctly, it is very likely that a full table scan will be performed instead of an index. The possible_key, key_len, and key parameters in Explain can analyze whether our SQL statements use indexes.

The following situations will cause the index to fail

1. != is used in the query column, for example, select id,name,age from student where id != 2;
2. If a function operation is used in the query column, such as pow(id,2) to square the id, the index will not be used.
3. If there is an or in the condition, it will not be used even if some of the conditions have indexes (this is why you should try to use or as little as possible)
4. Even if the leftmost prefix principle is met in the joint index, the index will not be used if the first condition includes a range query.
5. If the data type of the index column is implicitly converted, the index will not be used. For example, if the column type is a string, the data must be quoted in the condition, otherwise the index will not be used.
6. If MySQL estimates that a full table scan will be faster than using an index, it does not use the index.

You can use Explain to test whether the index is used.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.