Shulou(Shulou.com)06/01 Report--

Editor to share with you a number of mysql joint index case studies, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

Index rules in MySQL:

1. The fields that need to be indexed are listed in the where condition.

2. Fields with small amount of data do not need to be indexed

3. If the where condition is an OR relation, indexing does not work.

4. Conform to the leftmost principle

What is a federated index?

A federated index is also called a composite index. For composite indexes: Mysql uses fields in the index from left to right, and a query can use only a portion of the index, but only the leftmost part. For example, the index is key index (a _ r _ b _ r _ c). It can be searched with three combinations of a | aPermian b | aPermian bPermec, but not bPermie c. The index is very effective when the leftmost field is a constant reference.

Indexes on two or more columns are called composite indexes

With additional columns in the index, you can narrow your search, but using an index with two columns is different from using two separate indexes. The structure of the composite index is similar to that of the phone book, where the first name consists of a last name and a first name. The phone book first sorts pairs by last name, and then sorts people with the same surname by first name. The phone book will be very useful if you know your last name; if you know your first name and first name, the phone book will be more useful, but if you only know your first name, the phone book will be useless.

So when creating a composite index, you should carefully consider the order of the columns. A composite index is useful when performing a search on all columns in an index or only on the first few columns; a composite index is not useful when searching only any subsequent column.

Creation of the index:

You can create an index when you execute a CREATE TABLE statement, or you can add an index to a table using CREATE INDEX or ALTER TABLE alone.

Case DEMO:

ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list) ALTER TABLE table_name ADD PRIMARY KEY (column_list) CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list) these are all the contents of the article "case study of multiple mysql Joint Indexes". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.