MySQL Federated Index Detailed

All MySQL column types can be indexed. The use of indexes on related columns is the best way to improve the performance of select operations.

First, prefix index

for char and varchar columns, you can index the prefix of the column. This is faster and requires less disk space than the entire column of the index. The syntax for indexed column prefixes in the CREATE TABLE statement looks like this:

KEY index_name (col_name (length))
The following example creates an index for the first 10 characters of the Name column:

mysql> CREATE TABLE Test (
Name CHAR (a) is not NULL,
KEY index_name (Name (10)));

For blobs and text columns, you must index the column prefixes, and you cannot index all of the columns.

Second, multi-column index
MySQL can create indexes on multiple columns. An index can consist of up to 15 columns. (on char and varchar columns, you can also use the prefix of a column as part of an index).

A multi-column index can be thought of as a sorted array that contains values created by merging (CONCATENATE) indexed column values.

When you specify a known number for the first column in the index of a WHERE clause, MySQL uses a multicolumn index in this way to make the query very fast, even if you do not specify a value for the other columns.

Indexing principles
1. The fewer indexes the better
Cause: When you modify the data, each index is updated to reduce the write speed.
2. The narrowest field is placed on the left side of the key
3. Avoid file sort sorting, temporary tables, and table scans.


How MySQL optimizes order by ordering statements with indexes

MySQL can also use indexes to quickly perform ordering and grouping operations for order by and GROUP by statements.

The ORDER BY statement optimization for MySQL is achieved through index optimization:
1. Index optimization for ORDER by. If an SQL statement is like:
SELECT [Column1],[column2],.... From [TABLE] ORDER by [sort];
Indexing on the [Sort] field allows for an order by optimization using an index.
2, where + ORDER by index optimization, shape such as:
SELECT [Column1],[column2],.... From [TABLE] WHERE [COLUMNX] = [value] ORDER by [sort];
Establish a federated index (Columnx,sort) to implement order by optimization.
Note: If the columnx corresponds to more than one value, the index cannot be used to achieve an order by optimization, as in the following statement
SELECT [Column1],[column2],.... From [TABLE] WHERE [ColumnX] In ([Value1],[value2],...) ORDER By[sort];
3. where+ Multiple fields ORDER by
SELECT * from [table] WHERE uid=1 ORDER x, y LIMIT 0, 10;
Indexing (Uid,x,y) achieves an order by optimization, which is much better than establishing a (X,Y,UID) index.
MySQL ORDER by cannot use an index to optimize the sorting situation
* ORDER by for different index keys: (key1,key2 indexed separately)
SELECT * from T1 ORDER by Key1, Key2;
* Do an ORDER by on a non-contiguous index key section: (Key_part1,key_part2 to establish a federated index; Key2 index)
SELECT * from T1 WHERE key2=constant ORDER by Key_part2;
* Both ASC and DESC are used: (Key_part1,key_part2 build Federated Index)
SELECT * from T1 ORDER by Key_part1 DESC, Key_part2 ASC;
* Index keys for searching records are not the same as ORDER by: (Key1,key2 indexed separately)
SELECT * from T1 WHERE key2=constant ORDER by Key1;
* If an expression (function) is applied on the field of where and order by, the index cannot be used to achieve an order by optimization
SELECT * from T1 ORDER by year (Logindate) LIMIT 0, 10;
Special tips:
1>mysql only one index can be used for a query. If you want to use indexes on multiple fields, create a composite index.
2> in an order by operation, MySQL uses the index only if the sort condition is not a query condition expression.

How to know if the SQL statement index in MySQL is in effect

MySQL function explain

Explain command explanation
Use explain to perform an inspection of SQL such as explain SELECT * from a +----+-------------+-------+-------+-------------------+---------+------- --+-------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len |ref | Rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | Simple | T3 | Const | primary,idx_t3_id | PRIMARY | 4 |   Const |       1 | |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ fourth column type This column is important to show which category the connection is using, and if there are any indexes to use.
The best to worst connection types are const, EQ_REG, ref, range, Indexhe, and all (1). System

This is a special case of the const join type. Only one row of the table satisfies the condition. 2). const

The table has a maximum of one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constants by the remainder of the optimizer. The const table is fast because they are read only once! 3). Eq_ref

For each row combination from the preceding table, a row is read from the table. This may be the best type of join, except for the const type. It is used in all parts of an index to be joined and the index is unique or PrimaryKey.

Eq_ref can be used for indexed columns that use the = operator comparison. The comparison value can be a constant or an expression that uses a column of a table that was read earlier in the table. (4). ref

For each row combination from the preceding table, all rows with matching index values are read from this table. If the join uses only the leftmost prefix of the key, or if the key is not unique or PRIMARYKEY (in other words, if the join cannot select a single row based on the keyword), ref is used. If you use a key that matches only a few rows, the join type is good.

Ref can be used with indexed columns that use the = or <=> operator. (5). Ref_or_null

The join type is like ref, but adding MySQL can specifically search for rows that contain null values. The optimization of the join type is often used in the resolution subquery.

In the following example, MySQL can use the Ref_or_null join to handle Ref_tables:

SELECT * from Ref_table
WHERE key_column=expr OR Key_column is NULL;

(6). Index_merge

The join type represents the use of the index merge optimization method. In this case, the key column contains the list of indexes used, and Key_len contains the longest key element for the index used.

For example:
Mysql> explain select * from T4 where id=3952602 oraccountid=31754306;
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+- -----+------+------------------------------------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+- -----+------+------------------------------------------------------+
| 1 | Simple | T4 | Index_merge | Idx_t4_id,idx_t4_accountid |idx_t4_id,idx_t4_accountid | bis |    null| 2 | Using Union (Idx_t4_id,idx_t4_accountid); Usingwhere |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+- -----+------+------------------------------------------------------+
1 row in Set (0.00 sec)

(7). Unique_subquery

This type replaces the ref of the in subquery in the following form:

Value in (SELECT primary_key from single_table wheresome_expr)
Unique_subquery is an index lookup function that can completely replace a subquery and be more efficient.

(8). Index_subquery

The join type is similar to Unique_subquery. You can replace in subqueries, but only for non-unique indexes in the following form of subqueries:

Value in (SELECT key_column from single_table wheresome_expr)

(9). Range

Retrieves only the rows for a given range, using an index to select rows. The key column shows which index is used. The Key_len contains the longest key element of the index being used. In this type, the ref column is null.

When you use the =, <>, >, >=, <, <=, ISNULL, <=>, between, or in operators to compare key columns with a constant, you can use the range

Mysql> explain select * from T3 where id=3952602 or id=3952603;
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len |ref | Rows | Extra |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| 1 | Simple | T3 | Range | primary,idx_t3_id | idx_t3_id | 4 |   NULL | 2 | Using where |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
1 row in Set (0.02 sec)

(Ten). Index

The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.

When a query uses only columns that are part of a single index, MySQL can use that join type.

(one). All

For each row combination from the previous table, complete the table scan. If the table is the first table that is not marked const, this is usually not good and is usually poor in its case. You can usually add more indexes instead of all, so that the rows can be retrieved based on the constant values or column values in the preceding table.

MySQL Federated Index Detailed