call mtr.add_suppression("mysql.outline");
call mtr.add_suppression("Statement outline");
create database outline_db;
create table outline_db.t1(id int auto_increment primary key,
col1 int,
col2 varchar(100),
key ind_1(col1),
key ind_2(col2)) engine = innodb;
create table outline_db.t2(id int auto_increment primary key,
col1 int,
col2 varchar(100),
key ind_1(col1),
key ind_2(col2)) engine = innodb;
insert into outline_db.t1 values(1, 2, 'xpchild');
insert into outline_db.t2 values(1, 2, 'xpchild');
commit;
create user 'u0'@'%';
grant all privileges on *.* to 'u0'@'%';
create user 'u1'@'%';
grant all privileges on outline_db.* to 'u1'@'%';
grant all privileges on mysql.* to 'u1'@'%';
show create table mysql.outline;
Table	Create Table
outline	CREATE TABLE `outline` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Digest` varchar(64) COLLATE utf8_bin NOT NULL,
  `Digest_text` longtext COLLATE utf8_bin,
  `Type` enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Scope` enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',
  `State` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
  `Position` bigint(20) NOT NULL,
  `Hint` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Statement outline'
2.1 check privileges
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
call dbms_outln.show_outline();
ID	SCHEMA	DIGEST	TYPE	SCOPE	POS	HINT	HIT	OVERFLOW	DIGEST_TEXT
#	outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	USE INDEX		1	ind_1	0	0	SELECT * FROM `t1` WHERE `col1` = ? AND `col2` = ?
call dbms_outln.flush_outline();
call dbms_outln.preview_outline('outline_db',
"select * from t1 where col1 = 2 and col2 = 'xxx'");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	TABLE	t1	1	USE INDEX (`ind_1`)
call dbms_outln.del_outline(1);
call dbms_outln.show_outline();
ID	SCHEMA	DIGEST	TYPE	SCOPE	POS	HINT	HIT	OVERFLOW	DIGEST_TEXT
call dbms_outln.add_index_outline('outline_db', 1, 'USE INDEX', 'ind_1', '',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR 42000: Incorrect number of arguments for PROCEDURE dbms_outln.add_index_outline; expected 7, got 6
call dbms_outln.add_index_outline('outline_db', 2, 0, 'USE INDEX', 'ind_1', '',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR HY000: The 2th parameter didn't match for native procedure dbms_outln.add_index_outline
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE xxx', 'ind_1', '',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR HY000: Statement outline 0 is not valid when add index outline since Outline type or position is invalid
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE xxx', 'ind_1', '',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR HY000: Statement outline 0 is not valid when add index outline since Outline type or position is invalid
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', 'FOR
                                  ORDER', "select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR HY000: Statement outline 0 is not valid when add index outline since Outline index's hint, digest, or scope is invalid
call dbms_outln.add_index_outline('outline_db', 'a7558f4dd3dd0713428fbd60beda118f35ed9c447ce58b2bb9239539fab76',
0, 'USE INDEX', 'ind_1, ind_2', 'FOR ORDER BY', "select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR HY000: Statement outline digest didn't match between a7558f4dd3dd0713428fbd60beda118f35ed9c447ce58b2bb9239539fab76 and 9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', 'FOR
                                  ORDER', "select * t1 where col1 = 2 and col2 = 'xpchild'");
ERROR HY000: Statement outline query parse error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 where col1 = 2 and col2 = 'xpchild'' at line 1
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1, ind_2',
'FOR ORDER BY', "select * from t1 where col1 = 2 and col2 = 'xpchild'");
call dbms_outln.show_outline();
ID	SCHEMA	DIGEST	TYPE	SCOPE	POS	HINT	HIT	OVERFLOW	DIGEST_TEXT
#	outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	USE INDEX	FOR ORDER BY	1	ind_1, ind_2	0	0	SELECT * FROM `t1` WHERE `col1` = ? AND `col2` = ?
select * from mysql.outline;
Id	Schema_name	Digest	Digest_text	Type	Scope	State	Position	Hint
#	outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	SELECT * FROM `t1` WHERE `col1` = ? AND `col2` = ?	USE INDEX	FOR ORDER BY	Y	1	ind_1, ind_2
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
call dbms_outln.add_optimizer_outline('outline_db', 1, 'OPTIMIZER', 'ind_1', '',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR 42000: Incorrect number of arguments for PROCEDURE dbms_outln.add_optimizer_outline; expected 5, got 6
call dbms_outln.add_optimizer_outline('outline_db', 2, 0, 'OPTIMIZER', 'ind_1', '',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR 42000: Incorrect number of arguments for PROCEDURE dbms_outln.add_optimizer_outline; expected 5, got 7
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ EXECUTION_TIME(1000) */',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR HY000: Statement outline optimizer hint parse error: /*+ EXECUTION_TIME(1000) */
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
call dbms_outln.show_outline();
ID	SCHEMA	DIGEST	TYPE	SCOPE	POS	HINT	HIT	OVERFLOW	DIGEST_TEXT
#	outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	OPTIMIZER		1	/*+ MAX_EXECUTION_TIME(1000) */	0	0	SELECT * FROM `t1` WHERE `col1` = ? AND `col2` = ?
select * from mysql.outline;
Id	Schema_name	Digest	Digest_text	Type	Scope	State	Position	Hint
#	outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	SELECT * FROM `t1` WHERE `col1` = ? AND `col2` = ?	OPTIMIZER		Y	1	/*+ MAX_EXECUTION_TIME(1000) */
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
alter table mysql.outline add col1 int;
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1, ind_2',
'FOR ORDER BY', "select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR HY000: Cannot load from mysql.outline. The table is probably corrupted
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
ERROR HY000: Cannot load from mysql.outline. The table is probably corrupted
call dbms_outln.flush_outline();
ERROR HY000: Cannot load from mysql.outline. The table is probably corrupted
call dbms_outln.del_outline(11111);
ERROR HY000: Cannot load from mysql.outline. The table is probably corrupted
alter table mysql.outline drop column col1;
call dbms_outln.del_outline(123456);
Warnings:
Warning	7537	Statement outline 123456 is not found in table
Warning	7537	Statement outline 123456 is not found in cache
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1, ind_2',
'FOR ORDER BY', "select * from t1 where col1 = 2 and col2 = 'xpchild'");
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
call dbms_outln.show_outline();
ID	SCHEMA	DIGEST	TYPE	SCOPE	POS	HINT	HIT	OVERFLOW	DIGEST_TEXT
#	outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	OPTIMIZER		1	/*+ MAX_EXECUTION_TIME(1000) */	0	0	SELECT * FROM `t1` WHERE `col1` = ? AND `col2` = ?
#	outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	USE INDEX	FOR ORDER BY	1	ind_1, ind_2	0	0	SELECT * FROM `t1` WHERE `col1` = ? AND `col2` = ?
update mysql.outline set  hint ='xxxx' where type ='OPTIMIZER';
commit;
call dbms_outln.flush_outline();
Warnings:
Warning	7533	Statement outline 5 is not valid when read outline since parse optimizer hint error
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
8.1 single table index hint
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1',
'FOR ORDER BY', "select * from t1 where col1 = 2 and col2 = 'xpchild' order by col1");
use outline_db;
explain select * from t1 where col1 = 2 and col2 = 'xpchild' order by col1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_1,ind_2	ind_1	5	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX FOR ORDER BY (`ind_1`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2)) order by `outline_db`.`t1`.`col1`
call dbms_outln.preview_outline('outline_db', "select * from t1 where col1 = 2 and col2 ='xpchild' order by col1");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	b4817bb6146950ee121e246f3aea2695a347202732feeea2d796f6c34f45bd94	TABLE	t1	1	USE INDEX FOR ORDER BY (`ind_1`)
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_2',
'', "select * from t1 where col1 = 2 and col2 = 'xpchild' order by col1");
use outline_db;
explain select * from t1 where col1 = 2 and col2 = 'xpchild' order by col1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_2`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2)) order by `outline_db`.`t1`.`col1`
call dbms_outln.preview_outline('outline_db', "select * from t1 where col1 = 2 and col2 ='xpchild' order by col1");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	b4817bb6146950ee121e246f3aea2695a347202732feeea2d796f6c34f45bd94	TABLE	t1	1	USE INDEX (`ind_2`)
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1',
'FOR ORDER BY', "select * from t1 where col1 = 2 and col2 = 'xpchild' order by col1");
use outline_db;
explain select * from t1 where col1 = 2 and col2 = 'xpchild' order by col1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_2`) USE INDEX FOR ORDER BY (`ind_1`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2)) order by `outline_db`.`t1`.`col1`
call dbms_outln.preview_outline('outline_db', "select * from t1 where col1 = 2 and col2 ='xpchild' order by col1");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	b4817bb6146950ee121e246f3aea2695a347202732feeea2d796f6c34f45bd94	TABLE	t1	1	USE INDEX (`ind_2`)
outline_db	b4817bb6146950ee121e246f3aea2695a347202732feeea2d796f6c34f45bd94	TABLE	t1	1	USE INDEX FOR ORDER BY (`ind_1`)
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1 ,ind_2',
'FOR ORDER BY', "select * from t1 where col1 = 2 and col2 = 'xpchild' order by col1");
use outline_db;
explain select * from t1 where col1 = 2 and col2 = 'xpchild' order by col1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_2`) USE INDEX FOR ORDER BY (`ind_1`) USE INDEX FOR ORDER BY (`ind_1`) USE INDEX FOR ORDER BY (`ind_2`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2)) order by `outline_db`.`t1`.`col1`
call dbms_outln.preview_outline('outline_db', "select * from t1 where col1 = 2 and col2 ='xpchild' order by col1");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	b4817bb6146950ee121e246f3aea2695a347202732feeea2d796f6c34f45bd94	TABLE	t1	1	USE INDEX (`ind_2`)
outline_db	b4817bb6146950ee121e246f3aea2695a347202732feeea2d796f6c34f45bd94	TABLE	t1	1	USE INDEX FOR ORDER BY (`ind_1`)
outline_db	b4817bb6146950ee121e246f3aea2695a347202732feeea2d796f6c34f45bd94	TABLE	t1	1	USE INDEX FOR ORDER BY (`ind_1`)
outline_db	b4817bb6146950ee121e246f3aea2695a347202732feeea2d796f6c34f45bd94	TABLE	t1	1	USE INDEX FOR ORDER BY (`ind_2`)
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
8.2 multi table index hint
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1',
'', "select t1.id, t2.id from t1, t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col1 = 1 and t1.col2 = 'xpchild'");
call dbms_outln.add_index_outline('outline_db', '', 2, 'USE INDEX', 'ind_1',
'', "select t1.id, t2.id from t1, t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col1 = 1 and t1.col2 = 'xpchild'");
use outline_db;
explain select t1.id, t2.id from t1, t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col1 = 1 and t1.col2 = 'xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_1	ind_1	5	const	1	100.00	Using where
1	SIMPLE	t2	NULL	ref	ind_1	ind_1	5	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t2`.`id` AS `id` from `outline_db`.`t1` USE INDEX (`ind_1`) join `outline_db`.`t2` USE INDEX (`ind_1`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t2`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t2`.`col1` = 1))
call dbms_outln.preview_outline('outline_db', "select t1.id, t2.id from t1, t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col1 = 1 and t1.col2 = 'xpchild'");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	134a74739eeae4ef189a05739f9bbeb28d8dd3006aa3634df5aeeb6757261748	TABLE	t1	1	USE INDEX (`ind_1`)
outline_db	134a74739eeae4ef189a05739f9bbeb28d8dd3006aa3634df5aeeb6757261748	TABLE	t2	2	USE INDEX (`ind_1`)
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_2',
'', "select t1.id, t2.id from t1, t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col1 = 1 and t1.col2 = 'xpchild'");
call dbms_outln.add_index_outline('outline_db', '', 2, 'USE INDEX', 'ind_2',
'', "select t1.id, t2.id from t1, t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col1 = 1 and t1.col2 = 'xpchild'");
use outline_db;
explain select t1.id, t2.id from t1, t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col1 = 1 and t1.col2 = 'xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
1	SIMPLE	t2	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t2`.`id` AS `id` from `outline_db`.`t1` USE INDEX (`ind_2`) join `outline_db`.`t2` USE INDEX (`ind_2`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t2`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t2`.`col1` = 1))
call dbms_outln.preview_outline('outline_db', "select t1.id, t2.id from t1, t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col1 = 1 and t1.col2 = 'xpchild'");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	134a74739eeae4ef189a05739f9bbeb28d8dd3006aa3634df5aeeb6757261748	TABLE	t1	1	USE INDEX (`ind_2`)
outline_db	134a74739eeae4ef189a05739f9bbeb28d8dd3006aa3634df5aeeb6757261748	TABLE	t2	2	USE INDEX (`ind_2`)
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
8.3 nest subquery index hint
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1',
'', "select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild'");
call dbms_outln.add_index_outline('outline_db', '', 2, 'USE INDEX', 'ind_1',
'', "select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild'");
use outline_db;
explain select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_1	ind_1	5	const	1	100.00	Using where
1	SIMPLE	t2	NULL	ref	ind_1	ind_1	5	const	1	100.00	Using where; FirstMatch(t1)
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id` from `outline_db`.`t1` USE INDEX (`ind_1`) semi join (`outline_db`.`t2` USE INDEX (`ind_1`)) where ((`outline_db`.`t2`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t2`.`col1` = 1) and (`outline_db`.`t1`.`col2` = 'xpchild'))
call dbms_outln.preview_outline("outline_db", "select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 ='xpchild') and col2 = 'xpchild'");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	248fb161f99dee2f4e53612f5fa6c2abc01fcae335dbb7c0eeea5593d27506b6	TABLE	t1	1	USE INDEX (`ind_1`)
outline_db	248fb161f99dee2f4e53612f5fa6c2abc01fcae335dbb7c0eeea5593d27506b6	TABLE	t2	2	USE INDEX (`ind_1`)
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_2',
'', "select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild'");
call dbms_outln.add_index_outline('outline_db', '', 2, 'USE INDEX', 'ind_2',
'', "select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild'");
use outline_db;
explain select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
1	SIMPLE	t2	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where; FirstMatch(t1)
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id` from `outline_db`.`t1` USE INDEX (`ind_2`) semi join (`outline_db`.`t2` USE INDEX (`ind_2`)) where ((`outline_db`.`t2`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t2`.`col1` = 1) and (`outline_db`.`t1`.`col2` = 'xpchild'))
call dbms_outln.preview_outline("outline_db", "select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 ='xpchild') and col2 = 'xpchild'");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	248fb161f99dee2f4e53612f5fa6c2abc01fcae335dbb7c0eeea5593d27506b6	TABLE	t1	1	USE INDEX (`ind_2`)
outline_db	248fb161f99dee2f4e53612f5fa6c2abc01fcae335dbb7c0eeea5593d27506b6	TABLE	t2	2	USE INDEX (`ind_2`)
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
8.4 index hint report error and overflow
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_3',
'', "select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild'");
select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild';
ERROR 42000: Key 'ind_3' doesn't exist in table 't1'
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
call dbms_outln.add_index_outline('outline_db', '', 3, 'USE INDEX', 'ind_1',
'', "select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild'");
select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild') and col2 = 'xpchild';
id
call dbms_outln.show_outline();
ID	SCHEMA	DIGEST	TYPE	SCOPE	POS	HINT	HIT	OVERFLOW	DIGEST_TEXT
#	outline_db	248fb161f99dee2f4e53612f5fa6c2abc01fcae335dbb7c0eeea5593d27506b6	USE INDEX		3	ind_1	0	1	SELECT `id` FROM `t1` WHERE `col1` IN ( SELECT `col1` FROM `t2` WHERE `col1` = ? AND `col2` = ? ) AND `col2` = ?
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
9.1 global optimizer hint
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
"select * from t1 where col1 = 2 and col2 = 'xpchild'");
use outline_db;
explain select * from t1 where col1 = 2 and col2 = 'xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_1,ind_2	ind_1	5	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select /*+ MAX_EXECUTION_TIME(1000) */ `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2))
call dbms_outln.preview_outline("outline_db", "select * from t1 where col1 = 2 and col2 = 'xpchild'");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	QUERY		1	/*+ MAX_EXECUTION_TIME(1000) */ 
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
"update t1 set col1 = 1");
use outline_db;
explain update t1 set col1 = 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	NULL	index	NULL	PRIMARY	4	NULL	1	100.00	NULL
Warnings:
Note	1003	update `outline_db`.`t1` set `outline_db`.`t1`.`col1` = 1
call dbms_outln.preview_outline("outline_db", "update t1 set col1 = 1");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
Warnings:
Warning	3125	MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
call dbms_outln.add_optimizer_outline('outline_db', '', 2, '/*+ MAX_EXECUTION_TIME(1000) */',
"select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild')");
use outline_db;
explain select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_1	ind_1	5	const	1	100.00	Using index
1	SIMPLE	t2	NULL	ref	ind_1,ind_2	ind_1	5	const	1	100.00	Using where; FirstMatch(t1)
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id` from `outline_db`.`t1` semi join (`outline_db`.`t2`) where ((`outline_db`.`t2`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t2`.`col1` = 1))
call dbms_outln.preview_outline("outline_db", "select id from t1 where col1 in (select col1 from t2 where col1 = 1 and col2 = 'xpchild')");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
Warnings:
Warning	3125	MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
9.2 multi-table join
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ BNL(t1,t2) */',
"select t1.id, t2.id from t1,t2");
use outline_db;
explain select t1.id, t2.id from t1,t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	ind_1	5	NULL	1	100.00	Using index
1	SIMPLE	t2	NULL	index	NULL	ind_1	5	NULL	1	100.00	Using index; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select /*+ BNL(`t1`@`select#1`) BNL(`t2`@`select#1`) */ `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t2`.`id` AS `id` from `outline_db`.`t1` join `outline_db`.`t2`
call dbms_outln.preview_outline("outline_db", "select t1.id, t2.id from t1,t2");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	cb19bfefd840f81c6f14048732ec4db814175d7b631c75c2f39923c595da026c	QUERY		1	/*+ BNL(`t1`@`select#1`) BNL(`t2`@`select#1`) */ 
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
"select t1.id, t2.id from t1,t2");
use outline_db;
explain select t1.id, t2.id from t1,t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	ind_1	5	NULL	1	100.00	Using index
1	SIMPLE	t2	NULL	index	NULL	ind_1	5	NULL	1	100.00	Using index; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select /*+ MAX_EXECUTION_TIME(1000) BNL(`t1`@`select#1`) BNL(`t2`@`select#1`) */ `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t2`.`id` AS `id` from `outline_db`.`t1` join `outline_db`.`t2`
call dbms_outln.preview_outline("outline_db", "select t1.id, t2.id from t1,t2");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	cb19bfefd840f81c6f14048732ec4db814175d7b631c75c2f39923c595da026c	QUERY		1	/*+ MAX_EXECUTION_TIME(1000) BNL(`t1`@`select#1`) BNL(`t2`@`select#1`) */ 
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
9.3 nest-subquery select
call dbms_outln.add_optimizer_outline('outline_db', '', 2, '/*+ INDEX_MERGE(t2 ind_1, ind_2) */',
"select t1.id from t1 where t1.col1 in (select t2.col1 from t2 where t2.col1 = 1 and t2.col2 = 'xpchild')");
use outline_db;
explain select t1.id from t1 where t1.col1 in (select t2.col1 from t2 where t2.col1 = 1 and t2.col2 = 'xpchild');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_1	ind_1	5	const	1	100.00	Using index
1	SIMPLE	t2	NULL	index_merge	ind_1,ind_2	ind_1,ind_2	5,403	NULL	1	100.00	Using intersect(ind_1,ind_2); Using where; Using index; FirstMatch(t1); Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select /*+ INDEX_MERGE(`t2`@`select#2` `ind_1`, `ind_2`) */ `outline_db`.`t1`.`id` AS `id` from `outline_db`.`t1` semi join (`outline_db`.`t2`) where ((`outline_db`.`t2`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t2`.`col1` = 1))
call dbms_outln.preview_outline("outline_db", "select t1.id from t1 where t1.col1 in (select t2.col1 from t2 where t2.col1 = 1 and t2.col2 = 'xpchild')");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	78032e49ecf095e565994e3e577eff0f1a9a2b74ae1509bedafde2fa8b3d9948	QUERY		1	/*+ INDEX_MERGE(`t2`@`select#2` `ind_1`, `ind_2`) */ 
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
9.4 subquery query block name
call dbms_outln.add_optimizer_outline('outline_db', '', 2, ' /*+ QB_NAME(subq1) */', "SELECT * FROM t1 WHERE t1.col1 IN (SELECT  col1 FROM t2)");
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ ', "SELECT * FROM t1 WHERE t1.col1 IN (SELECT  col1 FROM t2)");
use outline_db;
explain SELECT * FROM t1 WHERE t1.col1 IN (SELECT  col1 FROM t2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	ind_1	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t2	NULL	ref	ind_1	ind_1	5	outline_db.t1.col1	1	100.00	Using index; Start temporary; End temporary
Warnings:
Note	1003	/* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` semi join (`outline_db`.`t2`) where (`outline_db`.`t2`.`col1` = `outline_db`.`t1`.`col1`)
call dbms_outln.preview_outline("outline_db", "SELECT * FROM t1 WHERE t1.col1 IN (SELECT  col1 FROM t2)");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	14272202e294684578856e2b5cb4ecfbee542dfdbd3aa15e566d720167a0449c	QUERY		1	/*+ SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ 
outline_db	14272202e294684578856e2b5cb4ecfbee542dfdbd3aa15e566d720167a0449c	QUERY		2	/*+ QB_NAME(`subq1`) */ 
explain SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 WHERE t1.col1 IN (SELECT /*+ QB_NAME(subq1) */ col1 FROM t2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	ind_1	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t2	NULL	ref	ind_1	ind_1	5	outline_db.t1.col1	1	100.00	Using index; Start temporary; End temporary
Warnings:
Note	1003	/* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` semi join (`outline_db`.`t2`) where (`outline_db`.`t2`.`col1` = `outline_db`.`t1`.`col1`)
9.5 set var
call dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ SET_VAR(foreign_key_checks=OFF) */',
"insert into t1 values(123, 123, 'xpchild')");
use outline_db;
explain insert into t1 values(123, 123, 'xpchild');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	INSERT	t1	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Note	1003	insert /*+ SET_VAR(foreign_key_checks='OFF') */ into `outline_db`.`t1` values (123,123,'xpchild')
call dbms_outln.preview_outline("outline_db", "insert into t1 values(123, 123,'xpchild')");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	d683667dd9d9372e7a377442ac8ade18c6fb49e6d02197d56291262fb653aecc	QUERY		1	/*+ SET_VAR(foreign_key_checks='OFF') */ 
9.6 query trim
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_2',
'', "select * from t1 where col1 = 1 and col2='xpchild'");
call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_2',
'', "select * from t2 where col1 = 1 and col2='xpchild';");
explain select * from t1 where col1 = 1 and col2='xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_2`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 1))
explain select * from t2 where col1 = 1 and col2='xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t2`.`id` AS `id`,`outline_db`.`t2`.`col1` AS `col1`,`outline_db`.`t2`.`col2` AS `col2` from `outline_db`.`t2` USE INDEX (`ind_2`) where ((`outline_db`.`t2`.`col2` = 'xpchild') and (`outline_db`.`t2`.`col1` = 1))
explain select * from t1 where col1 = 1 and col2='xpchild';$
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_2`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 1))
explain select * from t2 where col1 = 1 and col2='xpchild';$
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ref	ind_2	ind_2	403	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t2`.`id` AS `id`,`outline_db`.`t2`.`col1` AS `col1`,`outline_db`.`t2`.`col2` AS `col2` from `outline_db`.`t2` USE INDEX (`ind_2`) where ((`outline_db`.`t2`.`col2` = 'xpchild') and (`outline_db`.`t2`.`col1` = 1))
call dbms_outln.show_outline();
ID	SCHEMA	DIGEST	TYPE	SCOPE	POS	HINT	HIT	OVERFLOW	DIGEST_TEXT
#	outline_db	9caffacb1c396360677d9a342b4978a21b79773a535c70ec70e859fcf2fa0ac1	USE INDEX		1	ind_2	2	0	SELECT * FROM `t1` WHERE `col1` = ? AND `col2` = ?
#	outline_db	2804907ae3ac34fbe9a3680666df613fdf12a4de76376ddbb2289e61e953479c	USE INDEX		1	ind_2	2	0	SELECT * FROM `t2` WHERE `col1` = ? AND `col2` = ?
call dbms_outln.preview_outline("outline_db", "select * from t2 where col1 = 1 and col2='xpchild'");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	2804907ae3ac34fbe9a3680666df613fdf12a4de76376ddbb2289e61e953479c	TABLE	t2	1	USE INDEX (`ind_2`)
call dbms_outln.preview_outline("outline_db", "select * from t2 where col1 = 1 and col2='xpchild';");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	2804907ae3ac34fbe9a3680666df613fdf12a4de76376ddbb2289e61e953479c	TABLE	t2	1	USE INDEX (`ind_2`)
call dbms_outln.preview_outline("outline_db", "select * from t2 where col1 = 1 and col2='xpchild';;;");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	2804907ae3ac34fbe9a3680666df613fdf12a4de76376ddbb2289e61e953479c	TABLE	t2	1	USE INDEX (`ind_2`)
10.1 explain format = xxx
CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',
"select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");
CALL dbms_outln.preview_outline('outline_db', "select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");
SCHEMA	DIGEST	BLOCK_TYPE	BLOCK_NAME	BLOCK	HINT
outline_db	f437c5dba64518c384b3dcb40cf16094994f963f1152f4ff5eac0b38f6276055	TABLE	t1	1	USE INDEX (`ind_1`)
use outline_db;
EXPLAIN SELECT * FROM t1 WHERE t1.col1 =2 AND t1.col2 ='xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_1	ind_1	5	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_1`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2))
EXPLAIN FORMAT = JSON SELECT * FROM t1 WHERE t1.col1 =2 AND t1.col2 ='xpchild';
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ref",
      "possible_keys": [
        "ind_1"
      ],
      "key": "ind_1",
      "used_key_parts": [
        "col1"
      ],
      "key_length": "5",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "416"
      },
      "used_columns": [
        "id",
        "col1",
        "col2"
      ],
      "attached_condition": "(`outline_db`.`t1`.`col2` = 'xpchild')"
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_1`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2))
EXPLAIN FORMAT = TRADITIONAL SELECT * FROM t1 WHERE t1.col1 =2 AND t1.col2 ='xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_1	ind_1	5	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_1`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2))
EXPLAIN FORMAT = 'JsOn' SELECT * FROM t1 WHERE t1.col1 =2 AND t1.col2 ='xpchild';
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ref",
      "possible_keys": [
        "ind_1"
      ],
      "key": "ind_1",
      "used_key_parts": [
        "col1"
      ],
      "key_length": "5",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "416"
      },
      "used_columns": [
        "id",
        "col1",
        "col2"
      ],
      "attached_condition": "(`outline_db`.`t1`.`col2` = 'xpchild')"
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_1`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2))
EXPLAIN FORMAT = 'TrAdItIoNaL' SELECT * FROM t1 WHERE t1.col1 =2 AND t1.col2 ='xpchild';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ind_1	ind_1	5	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_1`) where ((`outline_db`.`t1`.`col2` = 'xpchild') and (`outline_db`.`t1`.`col1` = 2))
delete from mysql.outline;
commit;
call dbms_outln.flush_outline();
drop database outline_db;
drop user 'u0'@'%';
drop user 'u1'@'%';