Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve repeated indexes and redundant indexes in mysql. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Duplicate index: indicates multiple indexes established on one column or several columns in the same order.

Redundant indexes: columns covered by two indexes overlap

Redundant indexes use index coverage in some special scenarios, so it is faster.

Scene

Such as articles and tag tables.

+-+

| | id | artid | tag | |

+-+

| | 1 | 1 | PHP |

| | 2 | 1 | Linux |

| | 3 | 2 | MySQl |

| | 4 | 2 | Oracle |

+-+

In practical use, there are two kinds of queries

Artid- query articles-tag

Tag- query articles-artid

SQL statement:

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

We can build redundant indexes to achieve index coverage, so that the query efficiency will be higher.

1. Set up an article tag table

There are two indexes in this table, one is at and the other is ta, and 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 '0mm, `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 has Using index, which means that index coverage is used here, and there is no need to return to query data after using index coverage, so the query is more efficient.

Select tag from T11 where artid = 1

The Extra in the query analysis of this statement has Using index, which means that index coverage is used here, and there is no need to return to query data after using index coverage, so the query is more efficient.

On how to achieve repeated indexes and redundant indexes in mysql is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.