128 lines
6.0 KiB
Plaintext
128 lines
6.0 KiB
Plaintext
create database explain_test_db;
|
|
use explain_test_db;
|
|
create table explain_test_table(c int) engine = XENGINE;
|
|
create procedure explain_test_proc()
|
|
BEGIN
|
|
select * from explain_test_table;
|
|
END //
|
|
call explain_test_proc();
|
|
c
|
|
insert into performance_schema.setup_objects values ('PROCEDURE',
|
|
'explain_test_db', 'explain_test_proc', 'YES', 'YES');
|
|
|
|
====================================================================
|
|
Testing index for columns OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
|
|
====================================================================
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "impossible";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ref OBJECT OBJECT 1 const # 100.00 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE > "impossible";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ALL OBJECT NULL NULL NULL # 33.33 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE < "2";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ALL OBJECT NULL NULL NULL # 33.33 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ref OBJECT OBJECT 1 const # 100.00 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "impossible"
|
|
and OBJECT_SCHEMA = "impossible";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ref OBJECT OBJECT 260 const,const # 100.00 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE"
|
|
and OBJECT_SCHEMA = "impossible";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ref OBJECT OBJECT 260 const,const # 100.00 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE"
|
|
and OBJECT_SCHEMA > "impossible";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ref OBJECT OBJECT 1 const # 33.33 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE"
|
|
and OBJECT_SCHEMA < "2";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ref OBJECT OBJECT 1 const # 33.33 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE"
|
|
and OBJECT_SCHEMA = "explain_test_db";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ref OBJECT OBJECT 260 const,const # 100.00 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "impossible"
|
|
and OBJECT_SCHEMA = "impossible"
|
|
and OBJECT_NAME = "impossible";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # NULL no matching row in const table
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE"
|
|
and OBJECT_SCHEMA = "explain_test_db"
|
|
and OBJECT_NAME = "impossible";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # NULL no matching row in const table
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE"
|
|
and OBJECT_SCHEMA = "explain_test_db"
|
|
and OBJECT_NAME > "impossible";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ref OBJECT OBJECT 260 const,const # 33.33 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE"
|
|
and OBJECT_SCHEMA = "explain_test_db"
|
|
and OBJECT_NAME < "2";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL ref OBJECT OBJECT 260 const,const # 33.33 Using where
|
|
############ Explain for Query ####################################
|
|
explain select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE"
|
|
and OBJECT_SCHEMA = "explain_test_db"
|
|
and OBJECT_NAME = "explain_test_proc";
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE setup_objects NULL const OBJECT OBJECT 518 const,const,const # 100.00 NULL
|
|
############# Explain End #########################################
|
|
flush status;
|
|
select ENABLED
|
|
from performance_schema.setup_objects
|
|
where OBJECT_TYPE = "PROCEDURE"
|
|
and OBJECT_SCHEMA = "explain_test_db"
|
|
and OBJECT_NAME = "explain_test_proc";
|
|
ENABLED
|
|
#
|
|
OK: handler_read_key incremented
|
|
delete from performance_schema.setup_objects where object_schema = 'explain_test_db';
|
|
drop table explain_test_db.explain_test_table;
|
|
drop database explain_test_db;
|