MySQL Federated index leftmost match example

MySQL Federated index leftmost match Example reference article: http:blog.jobbole.com/24006/Create a sample table. Sample table from MySQL Official document: https:dev.mysql.com/doc/employee/en/CREATE TABLE titles (emp_no INT not null,title VARCHAR (50) not null,from_date date not null,to_date date,primary KEY (emp_no,title,from_date)); Import test Data lo Ad_titles.dump https:github.com/datacharmer/test_db index condition MySQL> Show Index fromtitles;+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+-- ----+------------+------| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comme+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+-- ----+------------+------|          Titles | 0 |            PRIMARY | 1 | Emp_no |           A |     0 | NULL |      NULL | |      BTREE | |          Titles | 0 |            PRIMARY | 2 | Title |           A |     0 | NULL |      NULL | |      BTREE | |          Titles | 0 |            PRIMARY | 3 | From_date |           A |     0 | NULL |      NULL | |      BTREE | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+-- ----+------------+------3 RowsinchSet (0.00sec) Situation one. All columns in the Where condition are in the index MySQL> Desc SELECT * From the titles WHERE emp_no='10001'and title='Senior Engineer'and from_date='1986-06-26';+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+----- -+----------+-------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------  +------+----------+-------+| 1 | Simple | Titles | NULL | Const | PRIMARY | PRIMARY | 159 |    Const,const,const |   1 | 100.00 | NULL |+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+ ------+----------+-------+1 rowinchSet, 1 Warning (0.00sec) MySQL> Desc SELECT * From the titles WHERE title='Senior Engineer'and emp_no='10001'and from_date='1986-06-26';+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+----- -+----------+-------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------  +------+----------+-------+| 1 | Simple | Titles | NULL | Const | PRIMARY | PRIMARY | 159 |    Const,const,const |   1 | 100.00 | NULL |+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+ ------+----------+-------+1 rowinchSet, 1 warning (0.01sec) MySQL> Desc SELECT * From the titles WHERE from_date='1986-06-26'and title='Senior Engineer'and emp_no='10001';+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+----- -+----------+-------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------  +------+----------+-------+| 1 | Simple | Titles | NULL | Const | PRIMARY | PRIMARY | 159 |    Const,const,const |   1 | 100.00 | NULL |+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+ ------+----------+-------+1 rowinchSet, 1 Warning (0.00sec) MySQL> Desc SELECT * From the titles WHERE emp_no='10001'and titleinch('Senior Engineer',' Staff','Assistant Engineer') and from_date='1986-06-26';+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+ -------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+-----  -----+-------------+| 1 | Simple | Titles | NULL | Range | PRIMARY | PRIMARY | 159 |    NULL |   3 | 100.00 | Using where |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------ +----------+-------------+1 rowinchSet, 1 Warning (0.00sec) MySQL> Desc SELECT * From the titles WHERE Emp_noinch('10061','10062','10063','10064') and titleinch('Senior Engineer',' Staff','Assistant Engineer') and from_date='1986-06-26';+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+ -------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+-----  -----+-------------+| 1 | Simple | Titles | NULL | Range | PRIMARY | PRIMARY | 159 |   NULL |   12 | 100.00 | Using where |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------ +----------+-------------+1 rowinchSet, 1 Warning (0.00sec) MySQL> Desc SELECT * From the titles WHERE emp_no='10001'and title='Senior Engineer'and from_date>='1986-06-26';+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+ -------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+-----  -----+-------------+| 1 | Simple | Titles | NULL | Range | PRIMARY | PRIMARY | 159 |    NULL |   1 | 100.00 | Using where |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------ +----------+-------------+1 rowinchSet, 1 Warning (0.00sec) Situation two. All columns in the Where condition and order by are in the index MySQL> Desc SELECT * From the titles WHERE emp_no='10001'and title='Senior Engineer'order by From_date;+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----- -----+-------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------  +----------+-------------+| 1 | Simple | Titles | NULL | Ref | PRIMARY | PRIMARY | 156 |    Const,const |   1 | 100.00 | Using where |+----+-------------+--------+------------+------+---------------+---------+---------+-------------+ ------+----------+-------------+1 rowinchSet, 1 Warning (0.00sec) MySQL> Desc SELECT * From the titles WHERE emp_no='10001'order by Title,from_date;+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+ -------------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+-----  -----+-------------+| 1 | Simple | Titles | NULL | Ref | PRIMARY | PRIMARY | 4 |    Const |   1 | 100.00 | Using where |+----+-------------+--------+------------+------+---------------+---------+---------+-------+------ +----------+-------------+1 rowinchSet, 1 warning (0.01sec) MySQL> Desc SELECT *From the titles order by emp_no,title,from_date;+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+--------- -+-------+| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+---  -------+-------+| 1 | Simple | Titles | NULL | Index | NULL | PRIMARY | 159 | NULL |   441772 | 100.00 | NULL |+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+---- ------+-------+1 rowinchSet, 1 Warning (0.01 sec)

MySQL Federated index leftmost match example