MySQL column index and multi-column index

The MySQL database provides two types of indexes, and if not set correctly, the efficiency of the index will be greatly compromised.

CREATE TABLE Test (ID INT not NULL, last_name char (+) NOT NULL, first_name char (+) NOT NULL, P Rimary KEY (ID), INDEX name (Last_name,first_name));

The above is actually a multi-column index, the code to create the column index is as follows:

CREATE TABLE Test (ID INT not NULL, last_name char (+) NOT NULL, first_name char (+) NOT NULL, P Rimary KEY (ID), INDEX name (last_name), index_2 name (first_name));
A multicolumn index can be thought of as a sorted array that contains values created by merging (CONCATENATE) indexed column values. When the conditions of a query statement include last_name and first_name, for example:

SELECT * FROM Test WHERE last_name= ' Kun ' and first_name= ' Li ';

SQL will first filter out records that match the last_name criteria, and on top of them, filter first_name to match the criteria. So if we were to create two column indexes on last_name and first_name,

MySQL is handled differently, and it chooses the most rigorous index (with the fewest number of result set records ) to retrieve, which can be understood as the most powerful index to retrieve,

The other one is not used, so the effect is not as good as the multi-column index.

However, the use of multi-column indexes is also conditional, and the index cannot be used if the index is not started by the leftmost column "last Name" .

For example, a query statement in the following form can take advantage of a multi-column index:

SELECT * FROM Test WHERE last_name= ' Widenius '; SELECT * FROM Test WHERE last_name= ' Widenius ' and first_name= ' Michael '; SELECT * FROM Test WHERE last_name= ' Widenius ' and (first_name= ' Michael ' OR first_name= ' Monty '); SELECT * FROM Test WHERE last_name= ' Widenius ' and first_name >= ' M ' and first_name < ' N ';

However, query statements in the following form do not take advantage of multiple-column indexes ( because there is no starting condition in the query with the leftmost column last name )

SELECT * FROM Test WHERE first_name= ' Michael '; SELECT * FROM Test WHERE last_name= ' Widenius ' OR first_name= ' Michael ';

Dolegian indexes are more advantageous than indexes on each column, because the more indexes are built up, the more disk space is available, and the slower the data is to be updated.

In addition to multi-column indexing, the order is also important to note, the strict index should be placed in front, so that the intensity of filtering will be greater and more efficient.

The Dolegian index has the principle of the leftmost prefix (leftmost prefixing). Let's talk about this principle.

Now we have a multi-column index on the FirstName, LastName, and age columns, and we call this index fname_lname_age. When the search condition is a combination of the following columns,

MySQL will use the Fname_lname_age index:

Firstname,lastname,age

Firstname,lastname

FirstName

On the other hand, it is equivalent to the index we created (Firstname,lastname,age), (Firstname,lastname), and (FirstName) on these column combinations.

The following queries all have the ability to use this Fname_lname_age index:

SQL code

1. Select Peopleid from people
2. Where firstname=' Mike ' and lastname=' Sullivan ' and age=';

SQL code

1. Select Peopleid from people
2. Where firstname=' Mike ' and lastname=' Sullivan ';

SQL code

1. Select Peopleid from people
2. Where firstname=' Mike ';

The following queries are not able to use this fname_lname_age index:

SQL code

1. Select Peopleid from people
2. Where lastname=' Sullivan ';

SQL code

1. Select Peopleid from people
2. Where age=' n ';

SQL code

1. Select Peopleid from people
2. Where lastname=' Sullivan ' and age=';

One of the most important questions about how to build a multi-column index is how to arrange the order of the columns is critical,

The general rule can be said to be cardinality the larger the field should be ranked first in the index. Cardinality is the number of distinct index values that are generated if the column is indexed as an index key .

The higher the number of duplicate index values, the more that is the leftmost index column in the Multi-column index. Because it allows MySQL to filter out more rows.

In addition, if the selected field to be indexed is a string, such as varchar, text, blob, you can use the prefix index: that is, the number of bits in front of the field, not the full field, as an index (you can use the explain command

Take a look at whether the number of different records that are grouped together is close to each other, and the closer the better.

You can use the following explain statement to see which index is used, and how many records are obtained so that the index can be optimized.

Mysql> explain select product_id from Orders where order_id in (123, 312, 223,, 224) \g
1. Row ***************************
Id:1
Select_type:simple
Table:orders
Type:range
Possible_keys:orderid_productid
Key:orderid_productid
Key_len:5
Ref:null
Rows:5
Extra:using where; Using Index
1 row in Set (0.00 sec)

MySQL column index and multi-column index