Analysis of MySQL duplicate index and redundant index examples

This article uses examples to describe MySQL duplicate indexes and redundant indexes. Share with you for your reference, the details are as follows:

Duplicate index : refers to multiple indexes created on one column or several columns in the same order.

Redundant indexes : The columns covered by the two indexes overlap

Redundant indexes use index coverage in some special scenarios, so they are faster.

Scenario

For example, the article and tag table

+——+——-+——+
| id | artid | tag |
+——+——-+——+
| 1 | 1 | PHP |
| 2 | 1 | Linux |
| 3 | 2 | MySQl |
| 4 | 2 | Oracle |
+——+——-+——+

In actual use, there are two types of queries

• artid - query article - tag
• tag—query article’s—artid

SQL statement:

select tag from t11 where artid=2;
select artid from t11 where tag='PHP';

We can create redundant indexes to achieve index coverage, which will increase query efficiency.

1. Create an article tag table

There are two indexes in this table, one is at and the other is ta. Both indexes use the artid and tag fields.

CREATE TABLE `t16` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `artid` int(10) unsigned NOT NULL DEFAULT '0',
 `tag` char(20) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `at` (`artid`,`tag`),
 KEY `ta` (`tag`,`artid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

2. Test two SQL statements

select artid from t11 where tag='PHP';

The Extra in the query analysis of this statement contains Using index, which means that index coverage is used here. After using index coverage, there is no need to return rows to query data, so the query efficiency is relatively high.

select tag from t11 where artid = 1;

The Extra in the query analysis of this statement contains Using index, which means that index coverage is used here. After using index coverage, there is no need to return rows to query data, so the query efficiency is relatively high.

For more information about index coverage, please refer to the previous article: Index Coverage

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.