1. SQL is not indexed
Many times our slow query is due to no index . If there is no index, it will cause a full table scan. Therefore, you should consider when querying columns with conditions, build indexes, and try to avoid full table scans.
The statement that begins with EXPLAIN
in the image above is to get information about how MariaDB performs a SELECT, UPDATE, or DELETE. Looking at the type column you will see that it has a value of ALL
. Meaning it is scanning the entire table which is not recommended for large tables. There are also some other types you can see in the following figure:
In the ideal case we would do the following:
1 2 3 | // đánh index alter table users add index idx_name (name); |
Now look at the image above the type column has converted to the ref
value. To know what the ref
value means, you can see in the type section I shared above.
2. Index has no effect
Sometimes we explicitly add an index, but that index has no effect. So the index will not be valid in any cases?
2.1 Using query conditions other than data type
We create a users table as follows
1 2 | CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
The user_id field is a string type and is the global index of the B+ tree. If a number is used in the query condition the index will have no effect as shown below:
2.2 The query condition contains OR, which may cause the index to fail
We still use the same table structure as above
1 2 | CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
The user_id field is indexed, but age is not indexed. We use it in conjunction with OR and give the following result:
Note : If all fields of the OR condition are indexed, the index can be used or not, you can try it yourself. But when using, you still have to pay attention to this condition and use explain analysis. When faced with a similar situation, consider separating the two SQLs.
2.3. As special characters can make the index invalid
Not that using like + special character index will be invalid, but similar query starting with %
will cause index to fail.
2.4. The query condition does not meet the leftmost prefix of the common index
When MySQl creates a global index, it will follow the principle of leftmost prefix, i.e. the index will be used to search records based on the values of the fields from left to right. If you create a common (a,b,c)index, it is equivalent to creating (a), (a,b), (a,b,c) three indexes. Suppose you have the following table structure:
1 2 | CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_userid_name (user_id,name) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
There is a common index idx_userid_name, we execute this SQL, the query condition is invalid name
index:
Because the query condition column name
is not idx_userid_name the first column in the global index, the index is invalid. In a global index, the index is only valid when the query conditions satisfy the leftmost prefix.
2.5. Use MySQL’s built-in functions on indexed columns
Table structure:
1 2 | CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT,user_id varchar(32) NOT NULL,login_time datetime NOT NULL, PRIMARY KEY (id ), KEY idx_userId (user_id ) USING BTREE, KEY idx_login_time (login_Time) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; |
Although the login_time index is added, due to using the built-in mysql function Date_ADD()
, this index has no effect as shown in the figure:
2.6 Performing operations with index columns (such as +, -, , /), index will have no effect
Table structure:
1 2 | CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT,user_id varchar(32) NOT NULL,age int(11) DEFAULT NULL, PRIMARY KEY (id ), KEY idx_age (age) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; |
Although the age
field is already indexed, due to the following writing, the direct index is lost. . . As shown in the picture: