MySQL combined index and field order

Most of the time, we created an index in MySQL, but some queries are still slow, and no index is used at all! In general, some fields may not have been indexed, or the order of the fields in the composite index does not match the order of the fields in the query statement.

Look at the following example:
Suppose there is an order form (orders) that contains two fields of order_id and product_id.
A total of 31 data. There are 5 data that conform to the following statement. Execute the following SQL statement:

123	select product_idfrom orderswhere order_id in (123, 312, 223, 132, 224);

This statement takes MySQL to search according to order_id, and then returns the product_id in the matching record. Therefore, the composite index should be created in the following order:

1234567891011121314

create index orderid_productid on orders(order_id, product_id)mysql> explain select product_id from orders where order_id in (123, 312, 223, 132, 224) \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: orders         type: rangepossible_keys: orderid_productid          key: orderid_productid      key_len: 5          ref: NULL         rows: 5        Extra: Using where; Using index1row in set (0.00sec)

As you can see, this combination index is used, and the range of scans is small, only 5 lines. If you change the order of the combined index to product_id, order_id, MySQL will go to the index search *123 *312 *223 *132 *224, it will inevitably be a bit slower.

12345678910111213141516171819

mysql> create index orderid_productid on orders(product_id, order_id);                                                      Query OK, 31rows affected (0.01sec)Records: 31Duplicates: 0 Warnings: 0mysql> explain select product_id from orders where order_id in (123, 312, 223, 132, 224) \G*************************** 1. row ***************************            id: 1  select_type: SIMPLE        table: orders         type: indexpossible_keys: NULL          key: orderid_productid      key_len: 10          ref: NULL         rows: 31        Extra: Using where; Using index1row in set (0.00sec)

The performance of this index search obviously cannot be compared with the last time. Rows:31, I have a total of 31 data in my watch. The length of the portion of the index being used: Key_len:10, which is one times more than the last Key_len:5. Do not know how to find the index inside the fast, or directly to the full table scan faster?

12345678910111213141516171819

mysql> alter table orders add modify_a char(255) default‘aaa‘;Query OK, 31rows affected (0.01sec)Records: 31Duplicates: 0Warnings: 0mysql>mysql>mysql> explain select modify_a from orders where order_id in (123, 312, 223, 132, 224) \G         *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: orders         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 31        Extra: Using where1 row in set (0.00sec)

This will not use the index. Just because the product_id of select and the order_id in where are in the index.

Why create a composite index? Is it so simple to create a order_id index directly? If there is only one order_id index, there is nothing wrong with this index, and then MySQL goes to the table on the disk to fetch the product_id. If you have a combined index, MySQL can take the product_id completely from the index, and it will naturally be faster. Say a few more words on the leftmost precedence principle of the composite index:
The first field of the combined index must appear in the query group sentence, and the index will not be used. The result is a combined index (Col_a,col_b,col_c), which is used in the following situations:

1234	col_a = "some value";col_a = "some value"and col_b = "some value";col_a = "some value"and col_b = "some value"and col_c = "some value";col_b = "some value"and col_a = "some value"and col_c = "some value";

For the last statement, MySQL automatically optimizes the appearance of the third bar ~ ~. The following scenario does not use the index:

12	col_b = "aaaaaa";col_b = "aaaa"and col_c = "cccccc";

Column transfer from: http://hi.baidu.com/liuzhiqun/blog/item/4957bcb1aed1b5590823023c.html

Understand the single-column index, multicolumn index, and leftmost prefix principle through an instance. Example: Now we want to find out the user ID that meets the following criteria:
Mysql>select ' uid ' from people WHERELName ' = ' Liu ' and ' fname ' = ' zhiqun ' and ' age ' =26
because we do not want to scan the entire table, we consider using an index.

Single-column index:
alter TABLE people ADD INDEX lname (lname); indexes the lname column so that the scope is limited to the result set of Lname= ' Liu ' 1, then scans the result set 1, produces a result set that satisfies fname= ' Zhiqun ' 2, then scans the result set 2, finds the age=26 result set 3, which is the final result.

because of the index of the lname column, it is much more efficient than performing a full scan of the table, but the number of records we require to scan still goes far beyond what is actually needed. Although we can delete the index on the lname column, and then create an index of the fname or age column, the efficiency of the index search is still similar regardless of which column is created.

2. Multi-column index:
ALTER TABLE People ADD index lname_fname_age (lame,fname,age);

Note: When executing a query in MySQL, only one index can be used, and if we are building an index on lname,fname,age, we can only use one index when executing the query, and MySQL chooses the most rigorous index to get the lowest number of result set records.

3. Leftmost prefix: As the name implies, is the leftmost priority, the above example we created Lname_fname_age Multi-column index, equivalent to create a (lname) single-row index, (lname,fname) composite Index and (lname,fname,age) Combined index.

Note: When creating a multicolumn index, the most frequently used column in the WHERE clause is placed on the leftmost line, depending on the business requirements.

The time to build the index

Here we have learned to build an index, so where do we need to build the index? In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like. For example:

1	SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20AND m.city=‘郑州‘

The city and age need to be indexed, because the userame of the MyTable table also appears in the join clause, and it is necessary to index it.

Just now it is only necessary to index the like at certain times. Because MySQL does not use indexes when querying with wildcards% and _. For example, the following sentence will use the index:

1	SELECT * FROM mytable WHERE username like‘admin%‘

The following sentence will not be used:

1	SELECT * FROM mytable WHEREt Name like‘%admin‘

Therefore, you should pay attention to the above differences when using like.

The shortcomings of the index

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. So the index has its drawbacks as well:

• Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file.
• Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly. Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements.

Considerations for Using Indexes

There are some tips and considerations when working with indexes:

• The index does not contain a column with null values

This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.

• Use short Index

Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

• Indexed column Sorting

The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.

• Like statement operations

It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.

• Do not perform calculations on columns

Select * from the users where year (adddate) <2007;

The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to

Select * from users where adddate< ' 2007-01-01 ';

• Do not use not in and <> operations

MySQL combined index and field order