[Commits] fd8b31e3f48: Test updates #5

revision-id: fd8b31e3f48b075766b5a02d336f99356c0508d1 (mariadb-10.6.1-362-gfd8b31e3f48) parent(s): fc00182d74513287810949e06173599b63e12085 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-18 11:57:23 +0300 message: Test updates #5 --- mysql-test/main/selectivity_innodb.result | 462 ++++++++++----------- .../suite/gcol/r/innodb_virtual_basic.result | 4 +- mysql-test/suite/innodb/r/full_crc32_import.result | 4 +- .../suite/innodb/r/innodb_bulk_create_index.result | 48 +-- .../innodb/r/innodb_bulk_create_index_debug.result | 16 +- mysql-test/suite/innodb/r/innodb_mysql.result | 244 ++++++----- mysql-test/suite/innodb/t/full_crc32_import.test | 2 +- .../mysql-test/rocksdb/r/bloomfilter3.result | 12 +- .../rocksdb/mysql-test/rocksdb/r/rocksdb.result | 186 ++++----- .../rocksdb/mysql-test/rocksdb/t/bloomfilter3.test | 2 +- storage/rocksdb/mysql-test/rocksdb/t/rocksdb.test | 88 ++-- 11 files changed, 546 insertions(+), 522 deletions(-) diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 8db83c6bcaf..b344b91aa2e 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -32,14 +32,14 @@ test t1 a 1 9 0.4000 4.0000 1.2000 0 NULL NULL flush table t1; explain extended select * from t1 where a is null; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10 40.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 40.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is null explain extended select * from t1 where a is not null; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10 60.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 60.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is not null drop table t1; @@ -76,16 +76,16 @@ and r_name = 'ASIA' ) order by s_acctbal desc, n_name, s_name, p_partkey; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 0.31 Using where; Using temporary; Using filesort -1 PRIMARY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where -1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where -1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where -2 DEPENDENT SUBQUERY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where -2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 -2 DEPENDENT SUBQUERY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where -2 DEPENDENT SUBQUERY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY part NULL ALL PRIMARY NULL NULL NULL 200 0.31 Using where; Using temporary; Using filesort +1 PRIMARY region NULL ALL PRIMARY NULL NULL NULL 5 20.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY partsupp NULL ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +1 PRIMARY supplier NULL eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where +1 PRIMARY nation NULL eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where +2 DEPENDENT SUBQUERY region NULL ALL PRIMARY NULL NULL NULL 5 20.00 Using where +2 DEPENDENT SUBQUERY partsupp NULL ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 +2 DEPENDENT SUBQUERY supplier NULL eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where +2 DEPENDENT SUBQUERY nation NULL eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.part.p_partkey' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `dbt3_s001`.`supplier`.`s_acctbal` AS `s_acctbal`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`nation`.`n_name` AS `n_name`,`dbt3_s001`.`part`.`p_partkey` AS `p_partkey`,`dbt3_s001`.`part`.`p_mfgr` AS `p_mfgr`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`dbt3_s001`.`supplier`.`s_comment` AS `s_comment` from `dbt3_s001`.`part` join `dbt3_s001`.`supplier` join `dbt3_s001`.`partsupp` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`part`.`p_size` = 9 and `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey` and `dbt3_s001`.`region`.`r_name` = 'ASIA' and `dbt3_s001`.`part`.`p_type` like '%TIN' and `dbt3_s001`.`partsupp`.`ps_suppl ycost` = <expr_cache><`dbt3_s001`.`part`.`p_partkey`>((/* select#2 */ select min(`dbt3_s001`.`partsupp`.`ps_supplycost`) from `dbt3_s001`.`partsupp` join `dbt3_s001`.`supplier` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where `dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey` and `dbt3_s001`.`region`.`r_name` = 'ASIA' and `dbt3_s001`.`part`.`p_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`)) order by `dbt3_s001`.`supplier`.`s_acctbal` desc,`dbt3_s001`.`nation`.`n_name`,`dbt3_s001`.`supplier`.`s_name`,`dbt3_s001`.`part`.`p_partkey` @@ -117,16 +117,16 @@ and r_name = 'ASIA' ) order by s_acctbal desc, n_name, s_name, p_partkey; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where; Using temporary; Using filesort -1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 2.08 Using where; Using join buffer (flat, BNL join) -1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where -1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where -1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where -2 DEPENDENT SUBQUERY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where -2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 -2 DEPENDENT SUBQUERY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where -2 DEPENDENT SUBQUERY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY region NULL ALL PRIMARY NULL NULL NULL 5 20.00 Using where; Using temporary; Using filesort +1 PRIMARY part NULL ALL PRIMARY NULL NULL NULL 200 2.08 Using where; Using join buffer (flat, BNL join) +1 PRIMARY partsupp NULL ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +1 PRIMARY supplier NULL eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where +1 PRIMARY nation NULL eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where +2 DEPENDENT SUBQUERY region NULL ALL PRIMARY NULL NULL NULL 5 20.00 Using where +2 DEPENDENT SUBQUERY partsupp NULL ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 +2 DEPENDENT SUBQUERY supplier NULL eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where +2 DEPENDENT SUBQUERY nation NULL eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.part.p_partkey' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `dbt3_s001`.`supplier`.`s_acctbal` AS `s_acctbal`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`nation`.`n_name` AS `n_name`,`dbt3_s001`.`part`.`p_partkey` AS `p_partkey`,`dbt3_s001`.`part`.`p_mfgr` AS `p_mfgr`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`dbt3_s001`.`supplier`.`s_comment` AS `s_comment` from `dbt3_s001`.`part` join `dbt3_s001`.`supplier` join `dbt3_s001`.`partsupp` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`part`.`p_size` = 9 and `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey` and `dbt3_s001`.`region`.`r_name` = 'ASIA' and `dbt3_s001`.`part`.`p_type` like '%TIN' and `dbt3_s001`.`partsupp`.`ps_suppl ycost` = <expr_cache><`dbt3_s001`.`part`.`p_partkey`>((/* select#2 */ select min(`dbt3_s001`.`partsupp`.`ps_supplycost`) from `dbt3_s001`.`partsupp` join `dbt3_s001`.`supplier` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where `dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey` and `dbt3_s001`.`region`.`r_name` = 'ASIA' and `dbt3_s001`.`part`.`p_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`)) order by `dbt3_s001`.`supplier`.`s_acctbal` desc,`dbt3_s001`.`nation`.`n_name`,`dbt3_s001`.`supplier`.`s_name`,`dbt3_s001`.`part`.`p_partkey` @@ -146,12 +146,12 @@ from supplier, revenue0 where s_suppkey = supplier_no and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY supplier index PRIMARY PRIMARY 4 NULL 10 100.00 -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where -3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort -2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 229 100.00 -4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY supplier NULL index PRIMARY PRIMARY 4 NULL 10 100.00 +1 PRIMARY <derived3> NULL ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +3 DERIVED lineitem NULL range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort +2 SUBQUERY <derived4> NULL ALL NULL NULL NULL NULL 229 100.00 +4 DERIVED lineitem NULL range i_l_shipdate i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `dbt3_s001`.`supplier`.`s_suppkey` AS `s_suppkey`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`revenue0`.`total_revenue` AS `total_revenue` from `dbt3_s001`.`supplier` join `dbt3_s001`.`revenue0` where `revenue0`.`supplier_no` = `dbt3_s001`.`supplier`.`s_suppkey` and `revenue0`.`total_revenue` = (/* select#2 */ select max(`revenue0`.`total_revenue`) from `dbt3_s001`.`revenue0`) order by `dbt3_s001`.`supplier`.`s_suppkey` select s_suppkey, s_name, s_address, s_phone, total_revenue @@ -167,12 +167,12 @@ from supplier, revenue0 where s_suppkey = supplier_no and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY supplier index PRIMARY PRIMARY 4 NULL 10 100.00 -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where -3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort -2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 228 100.00 -4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY supplier NULL index PRIMARY PRIMARY 4 NULL 10 100.00 +1 PRIMARY <derived3> NULL ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +3 DERIVED lineitem NULL range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort +2 SUBQUERY <derived4> NULL ALL NULL NULL NULL NULL 228 100.00 +4 DERIVED lineitem NULL range i_l_shipdate i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `dbt3_s001`.`supplier`.`s_suppkey` AS `s_suppkey`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`revenue0`.`total_revenue` AS `total_revenue` from `dbt3_s001`.`supplier` join `dbt3_s001`.`revenue0` where `revenue0`.`supplier_no` = `dbt3_s001`.`supplier`.`s_suppkey` and `revenue0`.`total_revenue` = (/* select#2 */ select max(`revenue0`.`total_revenue`) from `dbt3_s001`.`revenue0`) order by `dbt3_s001`.`supplier`.`s_suppkey` select s_suppkey, s_name, s_address, s_phone, total_revenue @@ -196,10 +196,10 @@ and ps_suppkey not in (select s_suppkey from supplier where s_comment like '%Customer%Complaints%') group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 100.00 Using where; Using temporary; Using filesort -1 PRIMARY partsupp ref PRIMARY,i_ps_partkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; Using index -2 MATERIALIZED supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY part NULL ALL PRIMARY NULL NULL NULL 200 100.00 Using where; Using temporary; Using filesort +1 PRIMARY partsupp NULL ref PRIMARY,i_ps_partkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; Using index +2 MATERIALIZED supplier NULL ALL PRIMARY NULL NULL NULL 10 100.00 Using where Warnings: Note 1003 /* select#1 */ select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`part`.`p_brand` <> 'Brand#11' and `dbt3_s001`.`part`.`p_type` not like 'SMALL POLISHED%' and `dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8) and !<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */ select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where `dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%' ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where `dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_supp key`)))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt @@ -240,10 +240,10 @@ and ps_suppkey not in (select s_suppkey from supplier where s_comment like '%Customer%Complaints%') group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 16.67 Using where; Using temporary; Using filesort -1 PRIMARY partsupp ref PRIMARY,i_ps_partkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; Using index -2 MATERIALIZED supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY part NULL ALL PRIMARY NULL NULL NULL 200 16.67 Using where; Using temporary; Using filesort +1 PRIMARY partsupp NULL ref PRIMARY,i_ps_partkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; Using index +2 MATERIALIZED supplier NULL ALL PRIMARY NULL NULL NULL 10 100.00 Using where Warnings: Note 1003 /* select#1 */ select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`part`.`p_brand` <> 'Brand#11' and `dbt3_s001`.`part`.`p_type` not like 'SMALL POLISHED%' and `dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8) and !<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */ select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where `dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%' ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where `dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_supp key`)))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt @@ -284,10 +284,10 @@ and ps_suppkey not in (select s_suppkey from supplier where s_comment like '%Customer%Complaints%') group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 16.67 Using where; Using temporary; Using filesort -1 PRIMARY partsupp ref PRIMARY,i_ps_partkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; Using index -2 MATERIALIZED supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY part NULL ALL PRIMARY NULL NULL NULL 200 16.67 Using where; Using temporary; Using filesort +1 PRIMARY partsupp NULL ref PRIMARY,i_ps_partkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; Using index +2 MATERIALIZED supplier NULL ALL PRIMARY NULL NULL NULL 10 100.00 Using where Warnings: Note 1003 /* select#1 */ select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`part`.`p_brand` <> 'Brand#11' and `dbt3_s001`.`part`.`p_type` not like 'SMALL POLISHED%' and `dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8) and !<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */ select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where `dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%' ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where `dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_supp key`)))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt @@ -329,12 +329,12 @@ and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY orders ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 -1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 -1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index -2 MATERIALIZED lineitem index NULL PRIMARY 8 NULL 6005 100.00 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY orders NULL ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 +1 PRIMARY customer NULL eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 +1 PRIMARY lineitem NULL ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index +2 MATERIALIZED lineitem NULL index NULL PRIMARY 8 NULL 6005 100.00 Warnings: Note 1003 /* select#1 */ select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (/* select#2 */ select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where `dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey` and `<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` and `dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`, `dbt3_s001`.`orders`.`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` select @@ -363,12 +363,12 @@ and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY orders ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 -1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 -1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index -2 MATERIALIZED lineitem index NULL PRIMARY 8 NULL 6005 100.00 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY orders NULL ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 +1 PRIMARY customer NULL eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 +1 PRIMARY lineitem NULL ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index +2 MATERIALIZED lineitem NULL index NULL PRIMARY 8 NULL 6005 100.00 Warnings: Note 1003 /* select#1 */ select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (/* select#2 */ select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where `dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey` and `<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` and `dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`, `dbt3_s001`.`orders`.`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` select @@ -402,10 +402,10 @@ and not exists (select * from orders where o_custkey = c_custkey) ) as vip group by cntrycode order by cntrycode; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort -4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index -3 SUBQUERY customer ALL NULL NULL NULL NULL 150 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY customer NULL ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort +4 DEPENDENT SUBQUERY orders NULL ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index +3 SUBQUERY customer NULL ALL NULL NULL NULL NULL 150 100.00 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2 Note 1003 /* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,<expr_cache><`dbt3_s001`.`customer`.`c_custkey`>(exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey` limit 1))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) @@ -443,10 +443,10 @@ and not exists (select * from orders where o_custkey = c_custkey) ) as vip group by cntrycode order by cntrycode; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort -4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index -3 SUBQUERY customer ALL NULL NULL NULL NULL 150 91.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY customer NULL ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort +4 DEPENDENT SUBQUERY orders NULL ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index +3 SUBQUERY customer NULL ALL NULL NULL NULL NULL 150 91.00 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2 Note 1003 /* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,<expr_cache><`dbt3_s001`.`customer`.`c_custkey`>(exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey` limit 1))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) @@ -489,13 +489,13 @@ and s_nationkey = n_nationkey and n_name = 'UNITED STATES' order by s_name limit 10; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY supplier ALL PRIMARY,i_s_nationkey NULL NULL NULL 10 100.00 Using where; Using filesort -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -1 PRIMARY nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where -2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 100.00 Using where -2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where -4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY supplier NULL ALL PRIMARY,i_s_nationkey NULL NULL NULL 10 100.00 Using where; Using filesort +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY nation NULL eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where +2 MATERIALIZED part NULL ALL PRIMARY NULL NULL NULL 200 100.00 Using where +2 MATERIALIZED partsupp NULL ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +4 DEPENDENT SUBQUERY lineitem NULL ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 100.00 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 @@ -543,13 +543,13 @@ and s_nationkey = n_nationkey and n_name = 'UNITED STATES' order by s_name limit 10; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 4.17 Using where -2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where -4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.40 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY nation NULL ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort +1 PRIMARY supplier NULL ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED part NULL ALL PRIMARY NULL NULL NULL 200 4.17 Using where +2 MATERIALIZED partsupp NULL ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +4 DEPENDENT SUBQUERY lineitem NULL ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.40 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 @@ -599,13 +599,13 @@ and s_nationkey = n_nationkey and n_name = 'UNITED STATES' order by s_name limit 10; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 7.03 Using where -2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where -4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.40 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY nation NULL ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort +1 PRIMARY supplier NULL ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED part NULL ALL PRIMARY NULL NULL NULL 200 7.03 Using where +2 MATERIALIZED partsupp NULL ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +4 DEPENDENT SUBQUERY lineitem NULL ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.40 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 @@ -655,13 +655,13 @@ and s_nationkey = n_nationkey and n_name = 'UNITED STATES' order by s_name limit 10; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 7.81 Using where -2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where -4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.40 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY nation NULL ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort +1 PRIMARY supplier NULL ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED part NULL ALL PRIMARY NULL NULL NULL 200 7.81 Using where +2 MATERIALIZED partsupp NULL ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +4 DEPENDENT SUBQUERY lineitem NULL ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.40 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 @@ -711,13 +711,13 @@ and s_nationkey = n_nationkey and n_name = 'UNITED STATES' order by s_name limit 10; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 7.81 Using where -2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where -4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.40 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY nation NULL ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort +1 PRIMARY supplier NULL ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED part NULL ALL PRIMARY NULL NULL NULL 200 7.81 Using where +2 MATERIALIZED partsupp NULL ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +4 DEPENDENT SUBQUERY lineitem NULL ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.40 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 @@ -762,9 +762,9 @@ INSERT INTO t2 VALUES (1,9), (6,3), (2,8), (4,1), (0,7), (4,8), (4,5); EXPLAIN EXTENDED SELECT * FROM v1 INNER JOIN t2 ON ( a = c AND b = d ); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t2 ref idx idx 5 test.t1.b 1 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t2 NULL ref idx idx 5 test.t1.b 1 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t2`.`d` = `test`.`t1`.`b` SELECT * FROM v1 INNER JOIN t2 ON ( a = c AND b = d ); @@ -784,8 +784,8 @@ insert into t1 values set optimizer_use_condition_selectivity=1; explain extended select * from t1 where a < 1 and a > 7; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1 and `test`.`t1`.`a` > 7 select * from t1 where a < 1 and a > 7; @@ -793,8 +793,8 @@ a set optimizer_use_condition_selectivity=3; explain extended select * from t1 where a < 1 and a > 7; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1 and `test`.`t1`.`a` > 7 select * from t1 where a < 1 and a > 7; @@ -806,10 +806,10 @@ create table t2 (b int); insert into t2 values (2),(3); explain extended select * from t1 where a in ( select b from t2 ) AND ( a > 3 ); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t2 NULL ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t1`.`a` > 3 select * from t1 where a in ( select b from t2 ) AND ( a > 3 ); @@ -844,8 +844,8 @@ test.t1 analyze status OK flush table t1; set optimizer_use_condition_selectivity=4; explain extended select * from t1 where a=0; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1025 0.39 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1025 0.39 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 0 drop table t1; @@ -879,8 +879,8 @@ INSERT INTO t2 VALUES (1), (7); set optimizer_use_condition_selectivity=1; EXPLAIN EXTENDED SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +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 NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select 1 AS `1` from `test`.`t1` join `test`.`t2` where 0 SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10; @@ -888,8 +888,8 @@ SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10; set optimizer_use_condition_selectivity=3; EXPLAIN EXTENDED SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +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 NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select 1 AS `1` from `test`.`t1` join `test`.`t2` where 0 SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10; @@ -913,8 +913,8 @@ FLUSH TABLES; set optimizer_use_condition_selectivity=3; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 3; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 75.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 75.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3 SELECT * FROM t1 WHERE a > 3; @@ -945,9 +945,9 @@ set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='index_condition_pushdown=off'; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a > 9; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range a a 5 NULL 1 0.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 5 NULL 1 0.00 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 8 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > 9 SELECT * FROM t1, t2 WHERE a > 9; @@ -994,11 +994,11 @@ FLUSH TABLES; set optimizer_use_condition_selectivity=3; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE EXISTS ( SELECT 1 FROM t1, t2 ) AND a != b OR a <= 4; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 14 100.00 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 14 100.00 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 7 100.00 +2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where <cache>(<in_optimizer>(1,exists(/* select#2 */ select 1 from `test`.`t1` join `test`.`t2` limit 1))) and `test`.`t1`.`a` <> `test`.`t2`.`b` or `test`.`t1`.`a` <= 4 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; @@ -1020,26 +1020,26 @@ FLUSH TABLE t1; set optimizer_use_condition_selectivity=3; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IS NULL; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 14 28.57 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 14 28.57 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is null EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IS NOT NULL; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 14 71.43 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 14 71.43 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is not null EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IS NULL OR a IS NOT NULL; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 14 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 14 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` is not null EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IS NULL OR a < 5; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 14 69.39 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 14 69.39 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` < 5 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; @@ -1147,8 +1147,8 @@ select count(*) from t1 where a between 5 and 7; count(*) 3 explain extended select * from t1 where a between 5 and 7; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10 25.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 25.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` between 5 and 7 alter table t1 change column a a int; @@ -1158,8 +1158,8 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK flush table t1; explain extended select * from t1 where a between 5 and 7; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10 25.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 25.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` between 5 and 7 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; @@ -1191,8 +1191,8 @@ i1 EXPLAIN EXTENDED SELECT * FROM t1 WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 ); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` left join `test`.`t3` on(`test`.`t3`.`c3` = 'b')) where 0 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; @@ -1256,10 +1256,10 @@ lang_group lang code country_group cont_group cont EXPLAIN EXTENDED SELECT * FROM language, country, continent WHERE country_group = lang_group AND lang_group IS NULL; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE country ALL NULL NULL NULL NULL 2 50.00 Using where -1 SIMPLE language ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (flat, BNL join) -1 SIMPLE continent ALL NULL NULL NULL NULL 6 100.00 Using join buffer (incremental, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE country NULL ALL NULL NULL NULL NULL 2 50.00 Using where +1 SIMPLE language NULL ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (flat, BNL join) +1 SIMPLE continent NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where `test`.`language`.`lang_group` = `test`.`country`.`country_group` and `test`.`country`.`country_group` is null set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; @@ -1304,10 +1304,10 @@ set optimizer_use_condition_selectivity=1; explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 262144 100.00 Using where -1 SIMPLE t2 ref c,d c 5 test.t1.b 5 100.00 -1 SIMPLE t3 ALL NULL NULL NULL NULL 262144 100.00 Using where; Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 262144 100.00 Using where +1 SIMPLE t2 NULL ref c,d c 5 test.t1.b 5 100.00 +1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 262144 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t3` where `test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`a` = `test`.`t2`.`d` and `test`.`t3`.`b` < 5 and `test`.`t1`.`a` < 2000 select * from t1, t2, t1 as t3 @@ -1321,10 +1321,10 @@ set optimizer_use_condition_selectivity=3; explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 262144 0.00 Using where -1 SIMPLE t2 ref c,d d 5 test.t3.a 7 100.00 -1 SIMPLE t1 ALL NULL NULL NULL NULL 262144 2.00 Using where; Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 262144 0.00 Using where +1 SIMPLE t2 NULL ref c,d d 5 test.t3.a 7 100.00 +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 262144 2.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t3` where `test`.`t1`.`b` = `test`.`t2`.`c` and `test`.`t2`.`d` = `test`.`t3`.`a` and `test`.`t3`.`b` < 5 and `test`.`t1`.`a` < 2000 select * from t1, t2, t1 as t3 @@ -1350,9 +1350,9 @@ INSERT INTO t2 VALUES (1), (2); set optimizer_use_condition_selectivity=3; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select 'foo' AS `a`,'foo' AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 1 SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ; @@ -1383,16 +1383,16 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain extended select * from t1 straight_join t2 where t1.a=t2.a and t1.a<10; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1.00 Using where -1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1000 1.00 Using where +1 SIMPLE t2 NULL ref a a 5 test.t1.a 10 100.00 Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` < 10 explain extended select * from t1 straight_join t2 where t1.a=t2.a and t2.a<10; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1.00 Using where -1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1000 1.00 Using where +1 SIMPLE t2 NULL ref a a 5 test.t1.a 10 100.00 Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` < 10 set histogram_size=@save_histogram_size; @@ -1416,9 +1416,9 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain extended select * from t2 a straight_join t2 b where a.a is null; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE a ALL NULL NULL NULL NULL 1000 100.00 Using where -1 SIMPLE b ALL NULL NULL NULL NULL 1000 100.00 Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE a NULL ALL NULL NULL NULL NULL 1000 100.00 Using where +1 SIMPLE b NULL ALL NULL NULL NULL NULL 1000 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`a`.`a` AS `a`,`test`.`a`.`b` AS `b`,`test`.`b`.`a` AS `a`,`test`.`b`.`b` AS `b` from `test`.`t2` `a` straight_join `test`.`t2` `b` where `test`.`a`.`a` is null set histogram_size=@save_histogram_size; @@ -1444,9 +1444,9 @@ set optimizer_use_condition_selectivity=3; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a <> 'USARussian' AND b IS NULL; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref PRIMARY,b b 5 const 1 100.00 Using where; Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref PRIMARY,b b 5 const 1 100.00 Using where; Using index +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` <> 'USARussian' and `test`.`t1`.`b` is null SELECT * FROM t1, t2 @@ -1468,8 +1468,8 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain extended select * from t1 where col1 > 'b' and col1 < 'e'; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where `test`.`t1`.`col1` > 'b' and `test`.`t1`.`col1` < 'e' select * from t1 where col1 > 'b' and col1 < 'e'; @@ -1503,8 +1503,8 @@ col1 c explain extended select * from t1 where col1 > 'b' and col1 < 'd'; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 8 28.57 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 28.57 Using where Warnings: Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where `test`.`t1`.`col1` > 'b' and `test`.`t1`.`col1` < 'd' select * from t2 where col1 > 'b' and col1 < 'd'; @@ -1512,16 +1512,16 @@ col1 c explain extended select * from t2 where col1 > 'b' and col1 < 'd'; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` > 'b' and `test`.`t2`.`col1` < 'd' select * from t2 where col1 < 'b' and col1 > 'd'; col1 explain extended select * from t2 where col1 < 'b' and col1 > 'd'; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` < 'b' and `test`.`t2`.`col1` > 'd' drop table t1,t2; @@ -1556,10 +1556,10 @@ insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9 (11,11),(12,12),(13,13),(14,14),(15,15); set @@optimizer_use_condition_selectivity=2; explain extended select * from t1 where a in (select max(a) from t1 group by b); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 NULL ALL NULL NULL NULL NULL 16 100.00 Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where `<subquery2>`.`max(a)` = `test`.`t1`.`a` select * from t1 where a in (select max(a) from t1 group by b); @@ -1582,10 +1582,10 @@ a b 15 15 set @@optimizer_use_condition_selectivity=1; explain extended select * from t1 where a in (select max(a) from t1 group by b); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> NULL eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 NULL ALL NULL NULL NULL NULL 16 100.00 Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where `<subquery2>`.`max(a)` = `test`.`t1`.`a` select * from t1 where a in (select max(a) from t1 group by b); @@ -1660,20 +1660,20 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK # Check what info the optimizer has about selectivities explain extended select * from t1 use index () where a in (17,51,5); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 2.90 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1000 2.90 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5) explain extended select * from t1 use index () where b=2; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 5.90 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1000 5.90 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`b` = 2 # Now, the equality is used for ref access, while the range condition # gives selectivity data explain extended select * from t1 where a in (17,51,5) and b=2; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 2.90 Using where; Using rowid filter +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref|filter b,a b|a 5|5 const 59 (3%) 2.90 Using where; Using rowid filter Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5) drop table t1; @@ -1718,8 +1718,8 @@ CREATE INDEX CountryName ON City(Country,Name); set @@optimizer_use_condition_selectivity=2; EXPLAIN SELECT * FROM City WHERE Country='FIN'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 7 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE City NULL ref Country,CountryPopulation,CountryName Country 3 const 7 Using index condition DROP DATABASE world; use test; CREATE TABLE t1 ( @@ -1750,8 +1750,8 @@ INSERT INTO t1 SELECT a + 640, b, c FROM t1; INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; EXPLAIN SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range b,b_2 b 4 NULL 226 Using where SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; a 2071 @@ -1779,10 +1779,10 @@ insert into t3 select seq-1,seq-1,seq-1 from seq_1_to_100 limit 30; set optimizer_use_condition_selectivity=1; explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range a a 10 NULL 11 100.00 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 10 NULL 11 100.00 Using index condition; Using where +1 SIMPLE t3 NULL eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 +1 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 @@ -1801,10 +1801,10 @@ b a a b set optimizer_use_condition_selectivity=2; explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range a a 10 NULL 11 11.00 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 10 NULL 11 11.00 Using index condition; Using where +1 SIMPLE t3 NULL eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 +1 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 @@ -1859,22 +1859,22 @@ explain SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id WHERE A.a=t1.a AND t2.b < 20); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index -2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index -2 DEPENDENT SUBQUERY t2 ref|filter a,b a|b 5|5 test.A.id 1 (19%) Using where; Using rowid filter +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t1 NULL index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY A NULL ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 NULL ref|filter a,b a|b 5|5 test.A.id 1 (19%) Using where; Using rowid filter EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 -1 SIMPLE B ref a a 5 const 1 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE A NULL const PRIMARY,a PRIMARY 4 const 1 +1 SIMPLE B NULL ref a a 5 const 1 Using index explain SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id WHERE A.a=t1.a AND t2.b < 20); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index -2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index -2 DEPENDENT SUBQUERY t2 ref|filter a,b a|b 5|5 test.A.id 1 (19%) Using where; Using rowid filter +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t1 NULL index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY A NULL ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 NULL ref|filter a,b a|b 5|5 test.A.id 1 (19%) Using where; Using rowid filter set optimizer_switch= @save_optimizer_switch; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1,t2; @@ -1906,8 +1906,8 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 66.67 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 66.67 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 81 SELECT HEX(a), b from t1 where t1.a >= 81; @@ -1930,21 +1930,21 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 40.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 40.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 SET optimizer_use_condition_selectivity=3; # filtered should show 25 % EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 25.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 25.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; @@ -2055,8 +2055,8 @@ foo bar EXPLAIN EXTENDED SELECT * FROM t1 WHERE f1 < 'm'; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 72.09 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 72.09 Using where Warnings: Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where `test`.`t1`.`f1` < 'm' CREATE TABLE t2 (f1 TEXT) ENGINE=InnoDB; @@ -2072,8 +2072,8 @@ foo bar EXPLAIN EXTENDED SELECT * FROM t2 WHERE f1 <> 'qux'; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`f1` AS `f1` from `test`.`t2` where `test`.`t2`.`f1` <> 'qux' DROP TABLE t1,t2; @@ -2143,9 +2143,9 @@ TEXT41 TEXT,TEXT42 TEXT,TEXT43 TEXT,TEXT44 TEXT,TEXT45 TEXT, TEXT46 TEXT,TEXT47 TEXT,TEXT48 TEXT,TEXT49 TEXT,TEXT50 TEXT ) ENGINE=InnoDB; EXPLAIN SELECT 1 FROM t1 NATURAL JOIN t1 AS t2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where -1 SIMPLE t2 hash_ALL NULL #hash#$hj 150 test.t1.TEXT1,test.t1.TEXT2,test.t1.TEXT3,test.t1.TEXT4,test.t1.TEXT5,test.t1.TEXT6,test.t1.TEXT7,test.t1.TEXT8,test.t1.TEXT9,test.t1.TEXT10,test.t1.TEXT11,test.t1.TEXT12,test.t1.TEXT13,test.t1.TEXT14,test.t1.TEXT15,test.t1.TEXT16,test.t1.TEXT17,test.t1.TEXT18,test.t1.TEXT19,test.t1.TEXT20,test.t1.TEXT21,test.t1.TEXT22,test.t1.TEXT23,test.t1.TEXT24,test.t1.TEXT25,test.t1.TEXT26,test.t1.TEXT27,test.t1.TEXT28,test.t1.TEXT29,test.t1.TEXT30,test.t1.TEXT31,test.t1.TEXT32,test.t1.TEXT33,test.t1.TEXT34,test.t1.TEXT35,test.t1.TEXT36,test.t1.TEXT37,test.t1.TEXT38,test.t1.TEXT39,test.t1.TEXT40,test.t1.TEXT41,test.t1.TEXT42,test.t1.TEXT43,test.t1.TEXT44,test.t1.TEXT45,test.t1.TEXT46,test.t1.TEXT47,test.t1.TEXT48,test.t1.TEXT49,test.t1.TEXT50 1 Using where; Using join buffer (flat, BNLH join) +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 Using where +1 SIMPLE t2 NULL hash_ALL NULL #hash#$hj 150 test.t1.TEXT1,test.t1.TEXT2,test.t1.TEXT3,test.t1.TEXT4,test.t1.TEXT5,test.t1.TEXT6,test.t1.TEXT7,test.t1.TEXT8,test.t1.TEXT9,test.t1.TEXT10,test.t1.TEXT11,test.t1.TEXT12,test.t1.TEXT13,test.t1.TEXT14,test.t1.TEXT15,test.t1.TEXT16,test.t1.TEXT17,test.t1.TEXT18,test.t1.TEXT19,test.t1.TEXT20,test.t1.TEXT21,test.t1.TEXT22,test.t1.TEXT23,test.t1.TEXT24,test.t1.TEXT25,test.t1.TEXT26,test.t1.TEXT27,test.t1.TEXT28,test.t1.TEXT29,test.t1.TEXT30,test.t1.TEXT31,test.t1.TEXT32,test.t1.TEXT33,test.t1.TEXT34,test.t1.TEXT35,test.t1.TEXT36,test.t1.TEXT37,test.t1.TEXT38,test.t1.TEXT39,test.t1.TEXT40,test.t1.TEXT41,test.t1.TEXT42,test.t1.TEXT43,test.t1.TEXT44,test.t1.TEXT45,test.t1.TEXT46,test.t1.TEXT47,test.t1.TEXT48,test.t1.TEXT49,test.t1.TEXT50 1 Using where; Using join buffer (flat, BNLH join) set join_cache_level=@tmp_jcl; drop table t1; # diff --git a/mysql-test/suite/gcol/r/innodb_virtual_basic.result b/mysql-test/suite/gcol/r/innodb_virtual_basic.result index 4e8adbd88e6..31a3c12510d 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_basic.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_basic.result @@ -362,8 +362,8 @@ Warnings: Note 1265 Data truncated for column 'col_time_key' at row 1 Note 1265 Data truncated for column 'col_time_key' at row 2 EXPLAIN SELECT col_INT_key FROM b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE b index NULL col_INT_key 5 NULL 2 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE b NULL index NULL col_INT_key 5 NULL 2 Using index SELECT col_INT_key FROM b; col_INT_key 0 diff --git a/mysql-test/suite/innodb/r/full_crc32_import.result b/mysql-test/suite/innodb/r/full_crc32_import.result index 677bff22d53..c9ca2f9d018 100644 --- a/mysql-test/suite/innodb/r/full_crc32_import.result +++ b/mysql-test/suite/innodb/r/full_crc32_import.result @@ -49,8 +49,8 @@ t1 CREATE TABLE `t1` ( ) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC UPDATE t1 set b = repeat("de", 100) where b = repeat("cd", 200); explain SELECT a FROM t1 where b = repeat("de", 100); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using where SELECT a FROM t1 where b = repeat("de", 100); a 3 diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index.result index ec7ce044cb7..e8dc04a613d 100644 --- a/mysql-test/suite/innodb/r/innodb_bulk_create_index.result +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index.result @@ -33,11 +33,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -205,11 +205,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -377,11 +377,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -550,11 +550,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -724,11 +724,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -897,11 +897,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result index 295a9f1bed8..830aa858daa 100644 --- a/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result @@ -84,8 +84,8 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE title = 'a10'; class id title 10 10 a10 @@ -217,8 +217,8 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE title = 'a10'; class id title 10 10 a10 @@ -350,8 +350,8 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE title = 'a10'; class id title 10 10 a10 @@ -486,8 +486,8 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition SELECT * FROM t1 WHERE title = 'a10'; class id title 10 10 a10 diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 309ca847efb..d081dc41f18 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -166,13 +166,13 @@ INDEX idx (a6,a7(239),a5) EXPLAIN SELECT a4 FROM t1 WHERE a6=NULL AND a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE t.a6=t.a6 AND t1.a6=NULL AND t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; create table t1m (a int) engine = MEMORY; create table t1i (a int); @@ -190,9 +190,9 @@ select min(7) from DUAL; min(7) 7 explain select min(7) from t2i join t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1i ALL NULL NULL NULL NULL 0 -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 +1 SIMPLE t2i NULL ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select min(7) from t2i join t1i; min(7) NULL @@ -206,9 +206,9 @@ select max(7) from DUAL; max(7) 7 explain select max(7) from t2i join t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1i ALL NULL NULL NULL NULL 0 -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 +1 SIMPLE t2i NULL ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select max(7) from t2i join t1i; max(7) NULL @@ -237,23 +237,23 @@ select 1, max(1) from t1i where 1=99; 1 max(1) 1 NULL explain select count(*), min(7), max(7) from t1m, t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1m system NULL NULL NULL NULL 0 Const row not found -1 SIMPLE t1i ALL NULL NULL NULL NULL 0 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1m NULL system NULL NULL NULL NULL 0 Const row not found +1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 select count(*), min(7), max(7) from t1m, t1i; count(*) min(7) max(7) 0 NULL NULL explain select count(*), min(7), max(7) from t1m, t2i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1m system NULL NULL NULL NULL 0 Const row not found -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1m NULL system NULL NULL NULL NULL 0 Const row not found +1 SIMPLE t2i NULL ALL NULL NULL NULL NULL 1 select count(*), min(7), max(7) from t1m, t2i; count(*) min(7) max(7) 0 NULL NULL explain select count(*), min(7), max(7) from t2m, t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2m system NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 0 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2m NULL system NULL NULL NULL NULL 1 +1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 select count(*), min(7), max(7) from t2m, t1i; count(*) min(7) max(7) 0 NULL NULL @@ -345,11 +345,11 @@ create table t1 (f1 int, f2 char(1), primary key(f1,f2)) stats_persistent=0; insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); alter table t1 drop primary key, add primary key (f2, f1); explain select distinct f1 a, f1 b from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL 4 Using index; Using temporary explain select distinct f1, f2 from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL 4 Using index drop table t1; CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), INDEX (name)); @@ -360,15 +360,15 @@ INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,name name 23 NULL 2 Using where; Using index -1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range PRIMARY,name name 23 NULL 2 Using where; Using index +1 SIMPLE t2 NULL ref fkey fkey 5 test.t1.id 1 Using index EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,name name 23 NULL 2 Using where; Using index -1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range PRIMARY,name name 23 NULL 2 Using where; Using index +1 SIMPLE t2 NULL ref fkey fkey 5 test.t1.id 1 Using index DROP TABLE t1,t2; CREATE TABLE t1 ( id int NOT NULL, @@ -395,8 +395,8 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK flush tables; EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref name name 22 const 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref name name 22 const 2 Using where; Using index SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept rs5 cs10 @@ -404,8 +404,8 @@ rs5 cs9 DELETE FROM t1; # Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746). EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref name name 22 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref name name 22 const # Using where; Using index SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept DROP TABLE t1; @@ -986,8 +986,8 @@ SELECT COUNT(*) FROM t1 WHERE acct_id=785; COUNT(*) 8702 EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range idx1,idx2 idx1 9 NULL 2 Using where; Using index INSERT INTO t2 SELECT * FROM t1; OPTIMIZE TABLE t2; Table Op Msg_type Msg_text @@ -1101,8 +1101,8 @@ COUNT(*) EXPLAIN SELECT COUNT(*) FROM t1 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL idx_b,idx_c NULL NULL NULL # Using where SELECT COUNT(*) FROM t1 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; COUNT(*) @@ -1110,8 +1110,8 @@ COUNT(*) EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 5,4 NULL # Using sort_union(idx_c,idx_b); Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index_merge idx_b,idx_c idx_c,idx_b 5,4 NULL # Using sort_union(idx_c,idx_b); Using where SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; COUNT(*) @@ -1192,11 +1192,11 @@ INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1; INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 5 NULL 128 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL b 5 NULL 128 EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 Using filesort DROP TABLE t1; drop table if exists t1; show variables like 'innodb_rollback_on_timeout'; @@ -1283,6 +1283,7 @@ EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a; id 1 select_type SIMPLE table t1 +partitions NULL type ref possible_keys bkey key bkey @@ -1312,6 +1313,7 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; id 1 select_type SIMPLE table t1 +partitions NULL type range possible_keys bkey key bkey @@ -1357,6 +1359,7 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; id 1 select_type SIMPLE table t1 +partitions NULL type range possible_keys bkey key bkey @@ -1407,6 +1410,7 @@ EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a; id 1 select_type SIMPLE table t2 +partitions NULL type ref possible_keys bkey key bkey @@ -1436,6 +1440,7 @@ EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; id 1 select_type SIMPLE table t2 +partitions NULL type ref possible_keys bkey key bkey @@ -1465,6 +1470,7 @@ EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; id 1 select_type SIMPLE table t2 +partitions NULL type ref possible_keys bkey key bkey @@ -1494,6 +1500,7 @@ EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; id 1 select_type SIMPLE table t2 +partitions NULL type ref possible_keys bkey key bkey @@ -1596,40 +1603,40 @@ UNIQUE KEY (c,b) ) engine=innodb; INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 Using filesort SELECT c,b,d FROM t1 GROUP BY c,b,d; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 Using filesort SELECT c,b,d FROM t1 ORDER BY c,b,d; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL c 8 NULL 3 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL c 8 NULL 3 SELECT c,b,d FROM t1 GROUP BY c,b; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b FROM t1 GROUP BY c,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL c 8 NULL 3 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL c 8 NULL 3 Using index SELECT c,b FROM t1 GROUP BY c,b; c b 1 1 @@ -1642,6 +1649,7 @@ EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; id 1 select_type SIMPLE table t1 +partitions NULL type ref possible_keys b key b @@ -1657,6 +1665,7 @@ EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; id 1 select_type SIMPLE table t1 +partitions NULL type ref possible_keys b key b @@ -1672,6 +1681,7 @@ EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC; id 1 select_type SIMPLE table t1 +partitions NULL type index possible_keys NULL key b @@ -1688,6 +1698,7 @@ EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC; id 1 select_type SIMPLE table t1 +partitions NULL type index possible_keys NULL key b @@ -1704,6 +1715,7 @@ EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC; id 1 select_type SIMPLE table t1 +partitions NULL type index possible_keys NULL key b @@ -1720,6 +1732,7 @@ EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC; id 1 select_type SIMPLE table t1 +partitions NULL type index possible_keys NULL key b @@ -1786,8 +1799,8 @@ CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB; INSERT INTO t1 VALUES (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL idx NULL NULL NULL 4 Using where; Using filesort SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; id type d 191 member 1 @@ -1802,8 +1815,8 @@ ENGINE=InnoDB; INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1; EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range t1_b t1_b 5 NULL 8 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range t1_b t1_b 5 NULL 8 Using where SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; a b c 8 1 1 @@ -1863,10 +1876,10 @@ set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='in_to_exists=on,materialization=off'; explain select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 Const row not found -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t1 NULL system NULL NULL NULL NULL 0 Const row not found +2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; End of 5.0 tests @@ -2164,8 +2177,8 @@ INSERT INTO t1 VALUES (4,1,3,'pk',NULL),(5,1,3,'c2',NULL), (2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL); EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 12 NULL 16 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL PRIMARY 12 NULL 16 Using where SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; vid tid idx name type 3 1 4 c_extra NULL @@ -2188,9 +2201,9 @@ FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ref c3,c2 c3 5 const 2 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 +2 DERIVED t1 NULL ref c3,c2 c3 5 const 2 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; @@ -2202,9 +2215,9 @@ FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ref c3,c2 c3 9 const 2 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 +2 DERIVED t1 NULL ref c3,c2 c3 9 const 2 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) @@ -2217,9 +2230,9 @@ FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ref c3,c2 c3 7 const 2 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 +2 DERIVED t1 NULL ref c3,c2 c3 7 const 2 Using where; Using filesort DROP TABLE t1; End of 5.1 tests # @@ -2237,9 +2250,9 @@ KEY a (a,b) insert into t1 values (0),(1),(2),(3),(4); insert into t2 select A.a + 10 *B.a, 1, 'filler' from t1 A, t1 B; explain select * from t1, t2 where t2.a=t1.a and t2.b + 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref a a 4 test.t1.a 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 NULL ref a a 4 test.t1.a 1 Using index condition select * from t1, t2 where t2.a=t1.a and t2.b + 1; a a b filler 0 0 1 filler @@ -2290,8 +2303,8 @@ create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb; insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C; # this must use key 'a', not PRIMARY: explain select a from t2 where a=b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 10 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL index NULL a 10 # 1000 Using where; Using index drop table t1, t2; # # Bug #40360: Binlog related errors with binlog off @@ -2342,6 +2355,7 @@ EXPLAIN SELECT c FROM bar WHERE b>2;; id 1 select_type SIMPLE table bar +partitions NULL type ALL possible_keys b key NULL @@ -2353,6 +2367,7 @@ EXPLAIN SELECT c FROM foo WHERE b>2;; id 1 select_type SIMPLE table foo +partitions NULL type ALL possible_keys b key NULL @@ -2364,6 +2379,7 @@ EXPLAIN SELECT c FROM foo2 WHERE b>2;; id 1 select_type SIMPLE table foo2 +partitions NULL type range possible_keys b key b @@ -2375,6 +2391,7 @@ EXPLAIN SELECT c FROM bar WHERE c>2;; id 1 select_type SIMPLE table bar +partitions NULL type ALL possible_keys PRIMARY key NULL @@ -2386,6 +2403,7 @@ EXPLAIN SELECT c FROM foo WHERE c>2;; id 1 select_type SIMPLE table foo +partitions NULL type ALL possible_keys PRIMARY key NULL @@ -2397,6 +2415,7 @@ EXPLAIN SELECT c FROM foo2 WHERE c>2;; id 1 select_type SIMPLE table foo2 +partitions NULL type index possible_keys PRIMARY key b @@ -2716,11 +2735,11 @@ test.t1 analyze status OK SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; c1 c2 c3 EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort CREATE TABLE t2 ( c1 int NOT NULL, c2 int NOT NULL, @@ -2729,8 +2748,8 @@ KEY (c1,c2), KEY (c3) ) ENGINE=InnoDB; explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ref c1,c3 c1 4 const 1 Using where; Using filesort DROP TABLE t1,t2; # # 36259: Optimizing with ORDER BY @@ -2752,14 +2771,14 @@ INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; ANALYZE TABLE t1; EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref i2 i2 8 const,const 1 Using where; Using filesort EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref i2 i2 8 const,const 1 Using where; Using filesort EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL {checked} Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL {checked} Using where DROP TABLE t1; # # Bug #47963: Wrong results when index is used @@ -2800,8 +2819,8 @@ c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; a b c EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; # # Bug #46175: NULL read_view and consistent read assertion @@ -2847,9 +2866,9 @@ insert into t2 values (1),(2),(3); analyze table t1; analyze table t2; explain select t1.* from t1 left join t2 using(f1) group by t1.f1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 -1 SIMPLE t2 ref f1 f1 4 test.t1.f1 1 Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 3 +1 SIMPLE t2 NULL ref f1 f1 4 test.t1.f1 1 Using index drop table t1,t2; # # @@ -2870,6 +2889,7 @@ EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 +partitions NULL type index possible_keys NULL key b @@ -2969,9 +2989,9 @@ id id NULL 75 EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0 WHERE t2.id=75 AND t1.id IS NULL; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1,t2; # # Bug#38999 valgrind warnings for update statement in function compare_record() @@ -3032,8 +3052,8 @@ EXPLAIN SELECT * FROM t1 WHERE f1 IN 3470988,3305175,3329875,3817277,3856380,3796193, 3784744,4180925,4559596,3963734,3856391,4494153) AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,60,4 NULL 1 Using intersect(idx2,idx1,PRIMARY); Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,60,4 NULL 1 Using intersect(idx2,idx1,PRIMARY); Using where set optimizer_switch=@tmp_innodb_mysql; DROP TABLE t1; # @@ -3065,8 +3085,8 @@ f1 f2 f3 f4 23 1 9918 1 EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE ORDER BY f1 DESC LIMIT 5; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range f2,f4 f4 1 NULL 22 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range f2,f4 f4 1 NULL 22 Using where DROP TABLE t1; # # Bug#54117 crash in thr_multi_unlock, temporary table @@ -3086,6 +3106,7 @@ EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 +partitions NULL type index possible_keys NULL key b @@ -3099,6 +3120,7 @@ EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 +partitions NULL type index possible_keys NULL key b @@ -3112,6 +3134,7 @@ EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 +partitions NULL type index possible_keys NULL key PRIMARY @@ -3125,6 +3148,7 @@ EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 +partitions NULL type index possible_keys NULL key PRIMARY @@ -3178,9 +3202,9 @@ EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL # -2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL # Using sort_union(idx,PRIMARY); Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> NULL ALL NULL NULL NULL # 1537 +2 DERIVED t1 NULL index_merge PRIMARY,idx idx,PRIMARY 5,4 # 1537 Using sort_union(idx,PRIMARY); Using where SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; @@ -3288,10 +3312,10 @@ INSERT INTO t1 VALUES ('1'), (NULL); INSERT INTO t2 VALUES (1, '1'); EXPLAIN SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t2 index NULL PRIMARY 4 NULL 1 Using index; FirstMatch(t1); Using join buffer (incremental, BNL join) +id select_type table partitions type possible_keys key key_len ref rows Extra +1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 1 +1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 NULL index NULL PRIMARY 4 NULL 1 Using index; FirstMatch(t1); Using join buffer (incremental, BNL join) SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); b 1 @@ -3324,8 +3348,8 @@ EXPLAIN SELECT v2 FROM t1 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i,v i 5 const 2 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref i,v i 5 const 2 Using where DROP TABLE t1; # # Bug#54606 innodb fast alter table + pack_keys=0 diff --git a/mysql-test/suite/innodb/t/full_crc32_import.test b/mysql-test/suite/innodb/t/full_crc32_import.test index c50e3899fc8..ce8cfff37ac 100644 --- a/mysql-test/suite/innodb/t/full_crc32_import.test +++ b/mysql-test/suite/innodb/t/full_crc32_import.test @@ -61,7 +61,7 @@ ALTER TABLE t1 IMPORT TABLESPACE; --enable_warnings SHOW CREATE TABLE t1; UPDATE t1 set b = repeat("de", 100) where b = repeat("cd", 200); ---replace_column 9 # +--replace_column 10 # explain SELECT a FROM t1 where b = repeat("de", 100); SELECT a FROM t1 where b = repeat("de", 100); SELECT COUNT(*) FROM t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter3.result b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter3.result index d447b480f44..17c6997f2f5 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter3.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter3.result @@ -31,14 +31,14 @@ test.linktable analyze status Engine-independent statistics collected test.linktable analyze status OK flush tables; explain select * from linktable; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE linktable ALL NULL NULL NULL NULL 10000 +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE linktable NULL ALL NULL NULL NULL NULL 10000 # This must use range(id1_type2), key_len=24 explain select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type2`) where id1 = 100 and link_type = 1 and time >= 0 and time <= 9223372036854775807 order by time desc; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE linktable range id1_type2 id1_type2 24 NULL 1000 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE linktable NULL range id1_type2 id1_type2 24 NULL 1000 Using where; Using index select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked'; select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type2`) where id1 = 100 and link_type = 1 and time >= 0 and time <= 9223372036854775807 order by time desc; id1 id2 link_type visibility data time version @@ -121,8 +121,8 @@ insert into t1 values (21,2,2,0x12FFFFFFFFFF,1); # restart explain select * from t1 where kp0=1 and kp1=1 and kp2=0x12FFFFFFFFFF order by kp3 desc; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref kp12 kp12 20 const,const,const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref kp12 kp12 20 const,const,const # Using where; Using index show status like '%rocksdb_bloom_filter_prefix%'; Variable_name Value Rocksdb_bloom_filter_prefix_checked 0 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result index 2dc3d02d4b1..e326979294c 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result @@ -70,11 +70,11 @@ a b 1 1 2 2 explain select * from t2 where a='no-such-key'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain select * from t2 where a='abc'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 const PRIMARY PRIMARY 12 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL const PRIMARY PRIMARY 12 const # select * from t2 where a='abc'; a b abc def @@ -189,8 +189,8 @@ insert into t8 values ('five', 'funf'); # Delete by PK explain delete from t8 where a='three'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t8 range PRIMARY PRIMARY 12 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t8 NULL range PRIMARY PRIMARY 12 NULL # Using where delete from t8 where a='three'; select * from t8; a col1 @@ -279,9 +279,9 @@ j 4 EXPLAIN SELECT * FROM t10, t11 WHERE i=j; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10 index PRIMARY PRIMARY 4 NULL # Using index -1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.i # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t10 NULL index PRIMARY PRIMARY 4 NULL # Using index +1 SIMPLE t11 NULL eq_ref PRIMARY PRIMARY 4 test.t10.i # Using index SELECT * FROM t10, t11 WHERE i=j; i j 1 1 @@ -531,22 +531,22 @@ insert into t30 values ('row2', 'row2-key', 'row2-data'); insert into t30 values ('row3', 'row3-key', 'row3-data'); explain select * from t30 where key1='row2-key'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 ref key1 key1 18 const # Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL ref key1 key1 18 const # Using index condition select * from t30 where key1='row2-key'; pk key1 col1 row2 row2-key row2-data explain select * from t30 where key1='row1'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 ref key1 key1 18 const # Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL ref key1 key1 18 const # Using index condition # This will produce nothing: select * from t30 where key1='row1'; pk key1 col1 explain select key1 from t30; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 index NULL key1 18 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL index NULL key1 18 NULL # Using index select key1 from t30; key1 row1-key @@ -575,8 +575,8 @@ insert into t30 values ('row5', 'row5-key', 'row5-data'); explain select * from t30 where key1 <='row3-key'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 range key1 key1 18 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # Using index condition select * from t30 where key1 <='row3-key'; pk key1 col1 row1 row1-key row1-data @@ -584,8 +584,8 @@ row2 row2-key row2-data row3 row3-key row3-data explain select * from t30 where key1 between 'row2-key' and 'row4-key'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 range key1 key1 18 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # Using index condition select * from t30 where key1 between 'row2-key' and 'row4-key'; pk key1 col1 row2 row2-key row2-data @@ -593,32 +593,32 @@ row3 row3-key row3-data row4 row4-key row4-data explain select * from t30 where key1 in ('row2-key','row4-key'); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 range key1 key1 18 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # Using index condition select * from t30 where key1 in ('row2-key','row4-key'); pk key1 col1 row2 row2-key row2-data row4 row4-key row4-data explain select key1 from t30 where key1 in ('row2-key','row4-key'); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 range key1 key1 18 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # Using where; Using index select key1 from t30 where key1 in ('row2-key','row4-key'); key1 row2-key row4-key explain select * from t30 where key1 > 'row1-key' and key1 < 'row4-key'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 range key1 key1 18 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # Using index condition select * from t30 where key1 > 'row1-key' and key1 < 'row4-key'; pk key1 col1 row2 row2-key row2-data row3 row3-key row3-data explain select * from t30 order by key1 limit 3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 index NULL key1 18 NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL index NULL key1 18 NULL # select * from t30 order by key1 limit 3; pk key1 col1 row1 row1-key row1-data @@ -626,8 +626,8 @@ row2 row2-key row2-data row3 row3-key row3-data explain select * from t30 order by key1 desc limit 3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 index NULL key1 18 NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL index NULL key1 18 NULL # select * from t30 order by key1 desc limit 3; pk key1 col1 row5 row5-key row5-data @@ -638,8 +638,8 @@ row3 row3-key row3-data # explain select * from t30 where pk <='row3'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 range PRIMARY PRIMARY 18 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL range PRIMARY PRIMARY 18 NULL # Using where select * from t30 where pk <='row3'; pk key1 col1 row1 row1-key row1-data @@ -647,8 +647,8 @@ row2 row2-key row2-data row3 row3-key row3-data explain select * from t30 where pk between 'row2' and 'row4'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 range PRIMARY PRIMARY 18 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL range PRIMARY PRIMARY 18 NULL # Using where select * from t30 where pk between 'row2' and 'row4'; pk key1 col1 row2 row2-key row2-data @@ -656,16 +656,16 @@ row3 row3-key row3-data row4 row4-key row4-data explain select * from t30 where pk in ('row2','row4'); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 range PRIMARY PRIMARY 18 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL range PRIMARY PRIMARY 18 NULL # Using where select * from t30 where pk in ('row2','row4'); pk key1 col1 row2 row2-key row2-data row4 row4-key row4-data explain select * from t30 order by pk limit 3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t30 index NULL PRIMARY 18 NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t30 NULL index NULL PRIMARY 18 NULL # select * from t30 order by pk limit 3; pk key1 col1 row1 row1-key row1-data @@ -1078,8 +1078,8 @@ insert into t1 values (-5,-5,-5); # INT column uses index-only: explain select key1 from t1 where key1=2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref key1 key1 5 const # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref key1 key1 5 const # Using index select key1 from t1 where key1=2; key1 2 @@ -1092,8 +1092,8 @@ insert into t2 values (1,1,1), (2,2,2); # INT UNSIGNED column uses index-only: explain select key1 from t2 where key1=2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref key1 key1 5 const # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ref key1 key1 5 const # Using index select key1 from t2 where key1=2; key1 2 @@ -1103,8 +1103,8 @@ insert into t3 values (1,1,1), (2,2,2); # BIGINT uses index-only: explain select key1 from t3 where key1=2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref key1 key1 9 const # Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t3 NULL ref key1 key1 9 const # Using index select key1 from t3 where key1=2; key1 2 @@ -1121,8 +1121,8 @@ key (key1) insert into t1 values(1, 'one',11), (2,'two',22); explain select key1 from t1 where key1='one'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref key1 key1 11 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref key1 key1 11 const # Using where; Using index # The following will produce no rows. This looks like a bug, # but it is actually correct behavior. Binary strings are end-padded # with \0 character (and not space). Comparison does not ignore @@ -1131,8 +1131,8 @@ select key1 from t1 where key1='one'; key1 explain select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref key1 key1 11 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref key1 key1 11 const # Using where; Using index select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0'; hex(key1) 6F6E6500000000000000 @@ -1146,8 +1146,8 @@ key (key1) insert into t2 values(1, 'one',11), (2,'two',22); explain select key1 from t2 where key1='one'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref key1 key1 11 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL ref key1 key1 11 const # Using where; Using index select key1 from t2 where key1='one'; key1 one @@ -1161,8 +1161,8 @@ key (key1) insert into t3 values(1, 'one',11), (2,'two',22); explain select key1 from t3 where key1='one'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref key1 key1 31 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t3 NULL ref key1 key1 31 const # Using where; Using index select key1 from t3 where key1='one'; key1 one @@ -1176,8 +1176,8 @@ key(key1) insert into t4 values(1, 'one'), (2,'two'),(3,'threee'),(55,'fifty-five'); explain select key1 from t4 where key1='two'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref key1 key1 13 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL ref key1 key1 13 const # Using where; Using index select key1 from t4 where key1='two'; key1 two @@ -1186,8 +1186,8 @@ key1 fifty-five explain select key1 from t4 where key1 between 's' and 'u'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range key1 key1 13 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t4 NULL range key1 key1 13 NULL # Using where; Using index select key1 from t4 where key1 between 's' and 'u'; key1 threee @@ -1856,15 +1856,15 @@ key(col2) comment 'cf4' insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); explain select * from t1 where col1=2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref col1 col1 5 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref col1 col1 5 const # select * from t1 where col1=2; pk col1 col2 2 2 2 explain select * from t1 where col2=3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref col2 col2 5 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref col2 col2 5 const # select * from t1 where col2=3; pk col1 col2 3 3 3 @@ -1886,8 +1886,8 @@ primary key (pk) comment 'cf5' insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); explain select * from t1 where col1=2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref col1 col1 5 const # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref col1 col1 5 const # select * from t1 where col1=2; pk col1 col2 2 2 2 @@ -1933,8 +1933,8 @@ key(id2) insert into t1 select A.a, B.a, 31, 1234 from t0 A, t0 B; explain select * from t1 where id1=30 and value1=30 for update; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 4 const # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 const # Using where set @var1=(select variable_value from information_schema.global_status where variable_name='rocksdb_number_keys_read'); @@ -1955,8 +1955,8 @@ create table t1 (id int primary key, value int) engine=rocksdb; insert into t1 values (1,1),(2,2),(3,3); # The following must not use 'Using filesort': explain select * from t1 ORDER BY id; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL # +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # drop table t1; # # Issue #26: Index-only scans for DATETIME and TIMESTAMP @@ -1989,8 +1989,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range kp1 kp1 6 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range kp1 kp1 6 NULL # Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2012,8 +2012,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 5 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2049,8 +2049,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01' and '2015-01-05'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range kp1 kp1 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range kp1 kp1 4 NULL # Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01' and '2015-01-05'; kp1 kp2 @@ -2072,8 +2072,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 3 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range kp1 kp1 3 NULL # Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2111,8 +2111,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range kp1 kp1 5 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range kp1 kp1 5 NULL # Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2134,8 +2134,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range kp1 kp1 4 NULL # Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2173,8 +2173,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range kp1 kp1 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range kp1 kp1 4 NULL # Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; kp1 kp2 @@ -2196,8 +2196,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 3 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range kp1 kp1 3 NULL # Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; kp1 kp2 @@ -2234,8 +2234,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2016' and '2020'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range kp1 kp1 2 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range kp1 kp1 2 NULL # Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '2016' and '2020'; kp1 kp2 @@ -2257,8 +2257,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2016' and '2020'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range kp1 kp1 1 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range kp1 kp1 1 NULL # Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '2016' and '2020'; kp1 kp2 @@ -2308,11 +2308,11 @@ a b c 2 2abcde 2abcde 3 3abcde 3abcde explain select * from t1 where b like '1%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 1258 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range b b 1258 NULL # Using where explain select b, a from t1 where b like '1%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 1258 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range b b 1258 NULL # Using where update t1 set b= '12345' where b = '2abcde'; select * from t1; a b c @@ -2369,8 +2369,8 @@ INSERT INTO t2 VALUES (1,1,1,1,1,1,1); SELECT * FROM t2 WHERE c1 > 4294967295 ORDER BY c1,c6; c1 c2 c3 c4 c5 c6 c7 EXPLAIN SELECT * FROM t2 WHERE c1 > 4294967295 ORDER BY c1,c6; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 50 Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 NULL range PRIMARY PRIMARY 4 NULL 50 Using where drop table t2; # # Issue #135: register transaction was not being called for statement @@ -2576,8 +2576,8 @@ c1 c2 c3 c1-val3 c2-val3 7 c1-val1 c2-val1 5 explain SELECT * FROM t1 force index(idx) WHERE c1 <> '1' ORDER BY c1 DESC; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx idx 32 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL range idx idx 32 NULL # Using where drop table t1; # # Issue#267: MyRocks issue with no matching min/max row and count(*) diff --git a/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter3.test b/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter3.test index dc2a0da506d..165da5d692f 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter3.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter3.test @@ -120,7 +120,7 @@ insert into t1 values (21,2,2,0x12FFFFFFFFFF,1); --source include/restart_mysqld.inc ---replace_column 9 # +--replace_column 10 # explain select * from t1 where kp0=1 and kp1=1 and kp2=0x12FFFFFFFFFF order by kp3 desc; show status like '%rocksdb_bloom_filter_prefix%'; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb.test b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb.test index f7de167bd96..5d5f90fc753 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb.test @@ -81,7 +81,7 @@ select * from t2; select * from t1; explain select * from t2 where a='no-such-key'; ---replace_column 9 # +--replace_column 10 # explain select * from t2 where a='abc'; select * from t2 where a='abc'; @@ -189,7 +189,7 @@ insert into t8 values ('five', 'funf'); --echo # Delete by PK ---replace_column 9 # +--replace_column 10 # explain delete from t8 where a='three'; delete from t8 where a='three'; @@ -264,7 +264,7 @@ INSERT INTO t11 VALUES (1),(4); select * from t10; select * from t11; ---replace_column 9 # +--replace_column 10 # EXPLAIN SELECT * FROM t10, t11 WHERE i=j; SELECT * FROM t10, t11 WHERE i=j; @@ -540,18 +540,18 @@ insert into t30 values ('row1', 'row1-key', 'row1-data'); insert into t30 values ('row2', 'row2-key', 'row2-data'); insert into t30 values ('row3', 'row3-key', 'row3-data'); ---replace_column 9 # +--replace_column 10 # explain select * from t30 where key1='row2-key'; select * from t30 where key1='row2-key'; ---replace_column 9 # +--replace_column 10 # explain select * from t30 where key1='row1'; --echo # This will produce nothing: select * from t30 where key1='row1'; ---replace_column 9 # +--replace_column 10 # explain select key1 from t30; select key1 from t30; @@ -576,37 +576,37 @@ insert into t30 values ('row4', 'row4-key', 'row4-data'), ('row5', 'row5-key', 'row5-data'); ---replace_column 9 # +--replace_column 10 # explain select * from t30 where key1 <='row3-key'; select * from t30 where key1 <='row3-key'; ---replace_column 9 # +--replace_column 10 # explain select * from t30 where key1 between 'row2-key' and 'row4-key'; select * from t30 where key1 between 'row2-key' and 'row4-key'; ---replace_column 9 # +--replace_column 10 # explain select * from t30 where key1 in ('row2-key','row4-key'); select * from t30 where key1 in ('row2-key','row4-key'); ---replace_column 9 # +--replace_column 10 # explain select key1 from t30 where key1 in ('row2-key','row4-key'); select key1 from t30 where key1 in ('row2-key','row4-key'); ---replace_column 9 # +--replace_column 10 # explain select * from t30 where key1 > 'row1-key' and key1 < 'row4-key'; select * from t30 where key1 > 'row1-key' and key1 < 'row4-key'; ---replace_column 9 # +--replace_column 10 # explain select * from t30 order by key1 limit 3; select * from t30 order by key1 limit 3; ---replace_column 9 # +--replace_column 10 # explain select * from t30 order by key1 desc limit 3; select * from t30 order by key1 desc limit 3; @@ -614,22 +614,22 @@ select * from t30 order by key1 desc limit 3; --echo # --echo # Range scans on primary key --echo # ---replace_column 9 # +--replace_column 10 # explain select * from t30 where pk <='row3'; select * from t30 where pk <='row3'; ---replace_column 9 # +--replace_column 10 # explain select * from t30 where pk between 'row2' and 'row4'; select * from t30 where pk between 'row2' and 'row4'; ---replace_column 9 # +--replace_column 10 # explain select * from t30 where pk in ('row2','row4'); select * from t30 where pk in ('row2','row4'); ---replace_column 9 # +--replace_column 10 # explain select * from t30 order by pk limit 3; select * from t30 order by pk limit 3; @@ -879,7 +879,7 @@ insert into t1 values (1,1,1); insert into t1 values (2,2,2); insert into t1 values (-5,-5,-5); --echo # INT column uses index-only: ---replace_column 9 # +--replace_column 10 # explain select key1 from t1 where key1=2; select key1 from t1 where key1=2; @@ -890,7 +890,7 @@ drop table t1; create table t2 (pk int primary key, key1 int unsigned, col1 int, key(key1)) engine=rocksdb; insert into t2 values (1,1,1), (2,2,2); --echo # INT UNSIGNED column uses index-only: ---replace_column 9 # +--replace_column 10 # explain select key1 from t2 where key1=2; select key1 from t2 where key1=2; @@ -900,7 +900,7 @@ drop table t2; create table t3 (pk bigint primary key, key1 bigint, col1 int, key(key1)) engine=rocksdb; insert into t3 values (1,1,1), (2,2,2); --echo # BIGINT uses index-only: ---replace_column 9 # +--replace_column 10 # explain select key1 from t3 where key1=2; select key1 from t3 where key1=2; @@ -916,7 +916,7 @@ create table t1 ( key (key1) ) engine=rocksdb; insert into t1 values(1, 'one',11), (2,'two',22); ---replace_column 9 # +--replace_column 10 # explain select key1 from t1 where key1='one'; --echo # The following will produce no rows. This looks like a bug, @@ -924,7 +924,7 @@ select key1 from t1 where key1='one'; --echo # with \0 character (and not space). Comparison does not ignore --echo # the tail of \0. select key1 from t1 where key1='one'; ---replace_column 9 # +--replace_column 10 # explain select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0'; select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0'; @@ -938,7 +938,7 @@ create table t2 ( key (key1) ) engine=rocksdb; insert into t2 values(1, 'one',11), (2,'two',22); ---replace_column 9 # +--replace_column 10 # explain select key1 from t2 where key1='one'; select key1 from t2 where key1='one'; @@ -952,7 +952,7 @@ create table t3 ( key (key1) ) engine=rocksdb; insert into t3 values(1, 'one',11), (2,'two',22); ---replace_column 9 # +--replace_column 10 # explain select key1 from t3 where key1='one'; select key1 from t3 where key1='one'; @@ -967,14 +967,14 @@ create table t4 ( ) engine=rocksdb; insert into t4 values(1, 'one'), (2,'two'),(3,'threee'),(55,'fifty-five'); ---replace_column 9 # +--replace_column 10 # explain select key1 from t4 where key1='two'; select key1 from t4 where key1='two'; select key1 from t4 where key1='fifty-five'; ---replace_column 9 # +--replace_column 10 # explain select key1 from t4 where key1 between 's' and 'u'; select key1 from t4 where key1 between 's' and 'u'; @@ -1315,12 +1315,12 @@ create table t1 ( insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); ---replace_column 9 # +--replace_column 10 # explain select * from t1 where col1=2; select * from t1 where col1=2; ---replace_column 9 # +--replace_column 10 # explain select * from t1 where col2=3; select * from t1 where col2=3; @@ -1342,7 +1342,7 @@ create table t1 ( ) engine=rocksdb; insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); ---replace_column 9 # +--replace_column 10 # explain select * from t1 where col1=2; select * from t1 where col1=2; @@ -1395,7 +1395,7 @@ create table t1 ( insert into t1 select A.a, B.a, 31, 1234 from t0 A, t0 B; ---replace_column 9 # +--replace_column 10 # explain select * from t1 where id1=30 and value1=30 for update; @@ -1419,7 +1419,7 @@ drop table t0,t1; create table t1 (id int primary key, value int) engine=rocksdb; insert into t1 values (1,1),(2,2),(3,3); --echo # The following must not use 'Using filesort': ---replace_column 9 # +--replace_column 10 # explain select * from t1 ORDER BY id; drop table t1; @@ -1443,7 +1443,7 @@ select date_add('2015-01-01 12:34:56', interval a day), a from t0; select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; @@ -1461,7 +1461,7 @@ create table t2 ( ) engine=rocksdb; insert into t2 select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; @@ -1484,7 +1484,7 @@ select date_add('2015-01-01', interval a day), a from t0; select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01' and '2015-01-05'; @@ -1502,7 +1502,7 @@ create table t2 ( ) engine=rocksdb; insert into t2 select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; @@ -1527,7 +1527,7 @@ select date_add('2015-01-01 12:34:56', interval a day), a from t0; select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; @@ -1545,7 +1545,7 @@ create table t2 ( ) engine=rocksdb; insert into t2 select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; @@ -1572,7 +1572,7 @@ select date_add('2015-01-01 09:00:00', interval a minute), a from t0; select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t1 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; @@ -1590,7 +1590,7 @@ create table t2 ( ) engine=rocksdb; insert into t2 select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t2 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; @@ -1616,7 +1616,7 @@ insert into t1 (kp1,kp2) select 2015+a, a from t0; select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2016' and '2020'; @@ -1634,7 +1634,7 @@ create table t2 ( ) engine=rocksdb; insert into t2 select * from t1; --echo # This must show 'Using index' ---replace_column 9 # +--replace_column 10 # explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2016' and '2020'; @@ -1679,9 +1679,9 @@ create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(b(1255))) en set global rocksdb_large_prefix=0; insert into t1 values (1, '1abcde', '1abcde'), (2, '2abcde', '2abcde'), (3, '3abcde', '3abcde'); select * from t1; ---replace_column 9 # +--replace_column 10 # explain select * from t1 where b like '1%'; ---replace_column 9 # +--replace_column 10 # explain select b, a from t1 where b like '1%'; update t1 set b= '12345' where b = '2abcde'; select * from t1; @@ -1889,7 +1889,7 @@ INSERT INTO t1 VALUES ('c1-val1','c2-val1',5); INSERT INTO t1 VALUES ('c1-val2','c2-val3',6); INSERT INTO t1 VALUES ('c1-val3','c2-val3',7); SELECT * FROM t1 force index(idx) WHERE c1 <> 'c1-val2' ORDER BY c1 DESC; ---replace_column 9 # +--replace_column 10 # explain SELECT * FROM t1 force index(idx) WHERE c1 <> '1' ORDER BY c1 DESC; drop table t1;