MySQL Federated Index Test

Build a test environment

1: CREATE TABLE

CREATE TABLE tab_index (id int (5), age int (3), DTE datetime);

2: Inserting test data

INSERT into Tab_indexvalues (1, ' 2012-05-13 ', +), insert into tab_indexvalues (2, ' 2012-05-13 ', +); INSERT INTO Tab_ Indexvalues (3, ' 2012-05-13 ', +), insert into Tab_indexvalues (4, ' 2012-05-13 ', +); insert into Tab_indexvalues (5, ' 2012-05-13 ', "," INSERT into tab_indexvalues (6, ' 2012-06-13 ', "); insert into tab_indexvalues (7, ' 2012-07-13 ', +); I Nsert into Tab_indexvalues (8, ' 2012-08-13 ', $); insert into tab_indexvalues (9, ' 2012-09-13 ', Notoginseng); INSERT INTO Tab_ Indexvalues (' 2012-05-17 '), insert into tab_indexvalues (one, ' 2012-05-19 ', "), insert into tab_indexvalues (1, ' Insert into Tab_indexvalues (2, ' 2012-05-13 ', at 2012-05-13), insert into Tab_indexvalues (3, ' 2012-05-13 ', to); Nsert into Tab_indexvalues (4, ' 2012-05-13 ', +); insert into Tab_indexvalues (5, ' 2012-05-13 ', +); INSERT INTO Tab_ Indexvalues (6, ' 2012-06-13 '), insert into tab_indexvalues (7, ' 2012-07-13 ', +), insert into tab_indexvalues (8, ' INSERT into Tab_indexvalues (9, ' 2012-09-13 ', Notoginseng), insert into tab_indexvalues (' 2012-05-17 ', 2012-08-13); Nsert INTO tab_indexvalues (one, ' 2012-05-19 ', "), insert into tab_indexvalues (1, ' 2012-05-13 ', +); insert into Tab_indexvalues (2, Insert into Tab_indexvalues (3, ' 2012-05-13 ', +); insert into Tab_indexvalues (4, ' 2012-05-13 ', +); I Nsert into Tab_indexvalues (5, ' 2012-05-13 ', "), insert into tab_indexvalues (6, ' 2012-06-13 ',"); INSERT into Tab_ Indexvalues (7, ' 2013-07-13 ', +), insert into tab_indexvalues (8, ' 2012-08-13 ', +); INSERT into tab_indexvalues (9, ' 2012-09-13 ', Panax Notoginseng); insert into tab_indexvalues (ten, ' 2012-05-17 ', max); insert into tab_indexvalues (one, ' 2011-05-19 ', "); I Nsert into Tab_indexvalues (1, ' 2012-05-13 ', +), insert into tab_indexvalues (2, ' 2012-05-13 ', +); INSERT INTO Tab_ Indexvalues (3, ' 2010-05-13 ', +), insert into Tab_indexvalues (4, ' 2012-05-13 ', +); insert into Tab_indexvalues (5, ' 2010-05-13 ', "," INSERT into tab_indexvalues (6, ' 2010-06-13 ', "); insert into tab_indexvalues (7, ' 2012-07-13 ', +); I Nsert into Tab_indexvalues (8, ' 2012-08-13 ', $); insert into tab_indexvalues (9, ' 2011-09-13 ', Notoginseng); INSERT INTO Tab_indeXVALUES (' 2012-05-17 '), insert into tab_indexvalues (one, ' 2012-05-19 ', "), insert into tab_indexvalues (1, ' Insert into Tab_indexvalues (2, ' 2012-05-13 ', at 2012-05-13), insert into Tab_indexvalues (3, ' 2012-05-13 ', to); Nsert into Tab_indexvalues (4, ' 2012-05-13 ', +); insert into Tab_indexvalues (5, ' 2012-05-13 ', +); INSERT INTO Tab_ Indexvalues (6, ' 2012-06-13 '), insert into tab_indexvalues (7, ' 2014-07-13 ', +), insert into tab_indexvalues (8, ' INSERT into Tab_indexvalues (9, ' 2011-09-13 ', Notoginseng), insert into tab_indexvalues (' 2012-05-17 ', 2012-08-13); Nsert into Tab_indexvalues (11, ' 2012-05-19 ', 39);

3: Create ID and Federated index on age

CREATE INDEX idx1on Tab_index (id,age);

4: Start Testing

Mysql> EXPLAIN SELECT id,dte,age, from    tab_index,    WHERE id=3;+----+-------------+-----------+--- ---+---------------+------+---------+-------+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref   | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+-------+------+------------- +|  1 | Simple      | tab_index | ref  | idx1          | idx1 | 5       | const |    5 | Using where |+----+-------------+-----------+------+---------------+------+---------+-------+------+------------ -+1 row in Set (0.00 sec)

Id=3 Walk index, age=31 not go index. It's easy to understand.

Mysql> EXPLAIN SELECT id,dte,age, from    tab_index,    WHERE age=31;+----+-------------+-----------+- -----+---------------+------+---------+------+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ |  1 | Simple      | tab_index | All  | NULL          | NULL | NULL    | NULL |   55 | Using where |+----+-------------+-----------+------+---------------+------+---------+------+------+------------- +1 row in Set (0.00 sec)

　　

Mysql> EXPLAIN SELECT id,dte,age, from    tab_index,    WHERE id=3 and age=31;+----+-------------+------ -----+------+---------------+------+---------+-------------+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------- ------+|  1 | Simple      | tab_index | ref  | idx1          | idx1 | |      const,const |    1 | Using where |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------ -------+1 Row in Set (0.00 sec)

　Id=3 and Age=31 and age=31 and id=3 all go indexed, but the index length is different from the previous

mysql> EXPLAIN SELECT id,dte,age    , from Tab_index    , WHERE age=31 and id=3; +----+-------------+------ -----+------+---------------+------+---------+-------------+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------- ------+|  1 | Simple      | tab_index | ref  | idx1          | idx1 | |      const,const |    1 | Using where |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------ -------+1 Row in Set (0.00 sec)

　　

Mysql> EXPLAIN SELECT id,dte,age, from    tab_index,    WHERE id=3 and age=31 and DTE between ' 2011-05-13 0 0:00:00 ' and ' 2013-05-13 00:00:00 ' +----+-------------+-----------+------+---------------+------+---------+------ -------+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------- ------+|  1 | Simple      | tab_index | ref  | idx1          | idx1 | |      const,const |    1 | Using where |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------ -------+1 Row in Set (0.00 sec)

　　

Mysql> EXPLAIN SELECT id,dte,age, from    tab_index,    WHERE id=3 and DTE between ' 2011-05-13 00:00:00 ' an D ' 2013-05-13 00:00:00 ' and age=31;+----+-------------+-----------+------+---------------+------+---------+------- ------+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------- ------+|  1 | Simple      | tab_index | ref  | idx1          | idx1 | |      const,const |    1 | Using where |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------ -------+1 Row in Set (0.00 sec)

　　

Mysql> EXPLAIN SELECT id,dte,age, from    Tab_index,    WHERE DTE between ' 2011-05-13 00:00:00 ' and ' 2013-0 5-13 00:00:00 ' and id=3 and age=31;+----+-------------+-----------+------+---------------+------+---------+-------- -----+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------- ------+|  1 | Simple      | tab_index | ref  | idx1          | idx1 | |      const,const |    1 | Using where |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------ -------+1 Row in Set (0.00 sec)

　　

Mysql> EXPLAIN SELECT id,dte,age, from    tab_index,    WHERE age=31 and id=3 and DTE between ' 2011-05-13 0 0:00:00 ' and ' 2013-05-13 00:00:00 ' +----+-------------+-----------+------+---------------+------+---------+------ -------+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------- ------+|  1 | Simple      | tab_index | ref  | idx1          | idx1 | |      const,const |    1 | Using where |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------ -------+1 Row in Set (0.00 sec)

　　

Mysql> EXPLAIN SELECT id,dte,age, from    tab_index,    WHERE age=31 and DTE between ' 2011-05-13 00:00:00 ' and ' 2013-05-13 00:00:00 ' and id=3, +----+-------------+-----------+------+---------------+------+---------+------- ------+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------- ------+|  1 | Simple      | tab_index | ref  | idx1          | idx1 | |      const,const |    1 | Using where |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------ -------+1 Row in Set (0.00 sec)

　　

Mysql> EXPLAIN SELECT id,dte,age, from    Tab_index,    WHERE DTE between ' 2011-05-13 00:00:00 ' and ' 2013-0 5-13 00:00:00 ' and age=31 and id=3, +----+-------------+-----------+------+---------------+------+---------+-------- -----+------+-------------+| ID | Select_type | Table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------- ------+|  1 | Simple      | tab_index | ref  | idx1          | idx1 | |      const,const |    1 | Using where |+----+-------------+-----------+------+---------------+------+---------+-------------+------+------ -------+1 Row in Set (0.00 sec)

In several other cases, students try to deepen their impressions.

Summarized as follows:

(ID) Walk the index with the shortest index length
(id,dte) walk index with shortest index length

(id,age) Go index, and the index length is the longest
(id,age,dte) Go index, and the index length is the longest
(id,dte,age) Go index, and the index length is the longest
(dte,id,age) Go index, and the index length is the longest
(dte,age,id) Go index, and the index length is the longest
(age,dte,id) Go index, and the index length is the longest
(age,id,dte) Go index, and the index length is the longest

MySQL Federated Index Test