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

This article introduces the relevant knowledge of "what is the difference between a combined index and a single-column index in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

What is a combined index?

Indexes on two or more columns are called federated indexes, and federated indexes are also called composite indexes. [related recommendation: mysql video tutorial]

For example, if the index is key index, it can support three combinations of a | aformab | aformai bpenc to search, but it does not support bmenade c to search. When using, the combination of an and c can also be used, but in fact only the index of an is used.

Create a composite index-- a common composite index create index index_name on table_name (column1, column2,...)-- A combination requires a unique index, with the [unique] keyword create unique index index_name on table_name (column1, column2,...) more than the previous one.

Delete composite index-usage 1drop index index_name on talbe_name-- usage 2alter table table_name drop index index_name-- usage 3, wrapper alter table table_name drop primary key single column index trap for 2

As shown in the figure, we have created two indexes. Using and to query in where is that only the index on the first condition is used in the execution plan, and the subsequent indexes are directly ignored.

Combined index trap

As shown in the picture, now set up the combinatorial index a _ rem _ b _ c

The query for the condition in the above figure is bmenaPhonec. MySQL will automatically change the order of the conditionality to arecoveryc, and then use the defined composite index.

The query condition of the above figure is bmaine a. Similarly, mysql adjusts the order of the conditions to aformai b, and then goes to the composite index.

The query condition in the above figure is bQuery c. Obviously, because an is not used as the condition column, mysql does not use a composite index.

Difference from a single-column index

The order of the columns in the federated index is very important, from the left principle.

A single index works one by one, that is, there are three single indexes, which conditional query works first, and the others do not work.

Composite index usage scenario

Where conditions occur frequently, and the number of current tables is relatively large.

The where condition is when and is used instead of or.

A federated index is more suitable than a single index, because the index takes up a certain amount of disk space, which means that there is a certain overhead. If there are more than one index, then more resources are wasted. A federated index is equivalent to building an index on multiple columns, and it is only built once, which is very suitable for and.

This is the end of the content of "what is the difference between a composite index and a single-column index in MySQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.