summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorMonty <monty@mariadb.org>2022-06-30 14:02:53 +0300
committerSergei Petrunia <sergey@mariadb.com>2023-02-02 23:08:23 +0300
commit013ba37ae23040ca804c1ac2c293cab4ccb5731e (patch)
treea2d50f7da82e20b67631efc71ba57b1353c39b67
parent59193ef67361354e8a5702ade8ce69d8b0ffe014 (diff)
downloadmariadb-git-013ba37ae23040ca804c1ac2c293cab4ccb5731e.tar.gz
Fix cost calculation in test_if_cheaper_ordering() to be cost based
The original code was mostly rule based and preferred clustered or covering indexed independent of cost. There where a few test changes: - Some test changed from using filesort to index or table scan. This happened when most of the rows had to be sorted and the ORDER BY could use covering or a clustered index (innodb_mysql, create_spatial_index). - Some test changed range to filesort. This where mainly because the range was scanning most of the rows or using index scan + row lookup and filesort with table scan is cheaper. (order_by). - Change in join_cache was because sorting 2 rows is faster than retrieving 10 rows. - In selectivity_innodb.test one test changed to use a cheaper index.
Diffstat
-rw-r--r--mysql-test/main/join_cache.result4
-rw-r--r--mysql-test/main/opt_trace.result85
-rw-r--r--mysql-test/main/opt_trace.test5
-rw-r--r--mysql-test/main/order_by.result59
-rw-r--r--mysql-test/main/order_by.test12
-rw-r--r--mysql-test/main/rowid_filter_innodb.result2
-rw-r--r--mysql-test/main/selectivity_innodb.result4
-rw-r--r--mysql-test/suite/innodb/r/innodb_mysql.result10
-rw-r--r--mysql-test/suite/innodb/r/partition_locking.result2
-rw-r--r--mysql-test/suite/innodb/t/partition_locking.test2
-rw-r--r--mysql-test/suite/innodb_gis/r/create_spatial_index.result63
-rw-r--r--mysql-test/suite/innodb_gis/t/create_spatial_index.test95
-rw-r--r--sql/filesort_utils.cc5
-rw-r--r--sql/filesort_utils.h5
-rw-r--r--sql/sql_select.cc462
15 files changed, 432 insertions, 383 deletions
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index fe40178a8da..2c5323a7795 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -5153,7 +5153,7 @@ SET SESSION optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN SELECT * FROM t1,t2
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0, 100) ORDER BY t1.f2 LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range f1,f2 f2 13 NULL 10 Using where
+1 SIMPLE t1 range f1,f2 f1 5 NULL 3 Using where; Rowid-ordered scan; Using filesort
1 SIMPLE t2 ref f3 f3 67 test.t1.f2 2 Using where; Using index
SELECT * FROM t1,t2
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1;
@@ -5163,7 +5163,7 @@ SET SESSION optimizer_switch = 'index_condition_pushdown=on';
EXPLAIN SELECT * FROM t1,t2
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range f1,f2 f2 13 NULL 10 Using where
+1 SIMPLE t1 range f1,f2 f1 5 NULL 3 Using index condition; Using where; Rowid-ordered scan; Using filesort
1 SIMPLE t2 ref f3 f3 67 test.t1.f2 2 Using where; Using index
SELECT * FROM t1,t2
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1;
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 121794cde92..19a1f607810 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1709,18 +1709,20 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a {
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "GROUP BY",
- "fanout": 1,
- "read_time": 2.084226263,
"table": "t1",
- "rows_estimation": 7,
+ "rows_estimation": 1,
+ "read_cost": 2.147624763,
+ "filesort_cost": 0.0633985,
+ "filesort_type": "priority_queue with addon fields",
+ "fanout": 1,
"possible_keys": [
{
"index": "a",
"can_resolve_order": true,
"direction": 1,
- "updated_limit": 7,
- "index_scan_cost": 2.084226263,
- "rows": 7,
+ "rows_to_examine": 7,
+ "range_scan": false,
+ "scan_cost": 2.084226263,
"chosen": true
}
]
@@ -2168,7 +2170,7 @@ a int,
b int,
c int,
filler char(100),
-KEY a_a(c),
+KEY c(c),
KEY a_c(a,c),
KEY a_b(a,b)
);
@@ -2183,6 +2185,10 @@ test.t1 analyze status OK
set optimizer_trace='enabled=on';
explain select * from t1 where a=1 and b=2 order by c limit 1;
id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a_b a_b 10 const,const 21 Using where; Using filesort
+update t1 set b=2 where pk between 20 and 40;
+explain select * from t1 where a=1 and b=2 order by c limit 1;
+id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a_c,a_b a_c 5 NULL 180 Using where
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
@@ -2268,7 +2274,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
},
"potential_range_indexes": [
{
- "index": "a_a",
+ "index": "c",
"usable": false,
"cause": "not applicable"
},
@@ -2302,8 +2308,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
- "rows": 21,
- "cost": 16.28742739,
+ "rows": 41,
+ "cost": 31.3040249,
"chosen": true
}
],
@@ -2320,11 +2326,11 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"range_access_plan": {
"type": "range_scan",
"index": "a_b",
- "rows": 21,
+ "rows": 41,
"ranges": ["(1,2) <= (a,b) <= (1,2)"]
},
- "rows_for_plan": 21,
- "cost_for_plan": 16.28742739,
+ "rows_for_plan": 41,
+ "cost_for_plan": 31.3040249,
"chosen": true
}
}
@@ -2334,8 +2340,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"rowid_filters": [
{
"key": "a_b",
- "build_cost": 1.127362357,
- "rows": 21
+ "build_cost": 1.752281351,
+ "rows": 41
},
{
"key": "a_c",
@@ -2348,7 +2354,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"selectivity_for_indexes": [
{
"index_name": "a_b",
- "selectivity_from_index": 0.021
+ "selectivity_from_index": 0.041
}
],
"selectivity_for_columns": [
@@ -2363,7 +2369,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"selectivity_from_histogram": 0.021
}
],
- "cond_selectivity": 0.021
+ "cond_selectivity": 0.041
}
]
},
@@ -2391,8 +2397,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"access_type": "ref",
"index": "a_b",
"used_range_estimates": true,
- "rows": 21,
- "cost": 16.26742739,
+ "rows": 41,
+ "cost": 31.2840249,
"chosen": true
},
{
@@ -2403,9 +2409,9 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
],
"chosen_access_method": {
"type": "ref",
- "records_read": 21,
- "records_out": 21,
- "cost": 16.26742739,
+ "records_read": 41,
+ "records_out": 41,
+ "cost": 31.2840249,
"uses_join_buffering": false
}
}
@@ -2415,15 +2421,15 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
{
"plan_prefix": [],
"table": "t1",
- "rows_for_plan": 21,
- "cost_for_plan": 16.26742739
+ "rows_for_plan": 41,
+ "cost_for_plan": 31.2840249
}
]
},
{
"best_join_order": ["t1"],
- "rows": 21,
- "cost": 16.26742739
+ "rows": 41,
+ "cost": 31.2840249
},
{
"substitute_best_equal": {
@@ -2445,28 +2451,29 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
- "fanout": 1,
- "read_time": 16.26742739,
"table": "t1",
- "rows_estimation": 21,
+ "rows_estimation": 41,
+ "read_cost": 32.58369415,
+ "filesort_cost": 1.299669251,
+ "filesort_type": "priority_queue with addon fields",
+ "fanout": 1,
"possible_keys": [
{
- "index": "a_a",
+ "index": "c",
"can_resolve_order": true,
"direction": 1,
- "updated_limit": 47,
- "index_scan_cost": 35.77753234,
- "usable": false,
- "cause": "cost"
+ "rows_to_examine": 24,
+ "range_scan": false,
+ "scan_cost": 18.51405907,
+ "chosen": true
},
{
"index": "a_c",
"can_resolve_order": true,
"direction": 1,
- "updated_limit": 47,
- "index_scan_cost": 35.78900415,
- "range_scan_cost": 6.375520747,
- "rows": 180,
+ "rows_to_examine": 4,
+ "range_scan": true,
+ "scan_cost": 10.5218905,
"chosen": true
},
{
@@ -2486,7 +2493,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
},
"potential_range_indexes": [
{
- "index": "a_a",
+ "index": "c",
"usable": false,
"cause": "not applicable"
},
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index cf54c8771d0..e9ff91dce43 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -148,7 +148,7 @@ create table t1 (
b int,
c int,
filler char(100),
- KEY a_a(c),
+ KEY c(c),
KEY a_c(a,c),
KEY a_b(a,b)
);
@@ -160,6 +160,9 @@ update t1 set b=2 where pk between 0 and 20;
analyze table t1;
set optimizer_trace='enabled=on';
explain select * from t1 where a=1 and b=2 order by c limit 1;
+
+update t1 set b=2 where pk between 20 and 40;
+explain select * from t1 where a=1 and b=2 order by c limit 1;
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
drop table t1,ten,one_k;
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 6db2b942e44..b59089bee80 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1552,16 +1552,71 @@ UNIQUE KEY a_c (a,c),
KEY (a));
INSERT INTO t1 VALUES (1, 10), (2, NULL);
# Must use ref-or-null on the a_c index
+ANALYZE FORMAT=JSON
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+ANALYZE
+{
+ "query_optimization": {
+ "r_total_time_ms": "REPLACED"
+ },
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "nested_loop": [
+ {
+ "read_sorted_file": {
+ "r_rows": 1,
+ "filesort": {
+ "sort_key": "t1.c",
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "r_used_priority_queue": false,
+ "r_output_rows": 1,
+ "r_buffer_size": "REPLACED",
+ "r_sort_mode": "sort_key,addon_fields",
+ "table": {
+ "table_name": "t1",
+ "access_type": "index",
+ "possible_keys": ["a_c", "a"],
+ "key": "a_c",
+ "key_length": "10",
+ "used_key_parts": ["a", "c"],
+ "r_loops": 1,
+ "rows": 2,
+ "r_rows": 2,
+ "r_table_time_ms": "REPLACED",
+ "r_other_time_ms": "REPLACED",
+ "filtered": 50,
+ "r_filtered": 50,
+ "attached_condition": "t1.a = 2 and (t1.c = 10 or t1.c is null)",
+ "using_index": true
+ }
+ }
+ }
+ }
+ ]
+ }
+}
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a_c,a a_c 10 NULL 2 Using where; Using index
+1 SIMPLE t1 index a_c,a a_c 10 NULL 2 Using where; Using index; Using filesort
# Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
col
1
+# With more rows "filesort" is removed
+INSERT INTO t1 select seq,seq from seq_1_to_2;
+EXPLAIN
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a_c,a a_c 10 NULL 2 Using where; Using index
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+col
+1
# With more rows "range" changes to "ref_or_null"
-INSERT INTO t1 select seq,seq from seq_1_to_10;
+INSERT INTO t1 select seq,seq from seq_3_to_10;
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index 81924a9b230..917f2737eec 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -912,13 +912,23 @@ CREATE TABLE t1 (
INSERT INTO t1 VALUES (1, 10), (2, NULL);
--echo # Must use ref-or-null on the a_c index
+--source include/analyze-format.inc
+ANALYZE FORMAT=JSON
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+
--echo # Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+--echo # With more rows "filesort" is removed
+INSERT INTO t1 select seq,seq from seq_1_to_2;
+EXPLAIN
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+
--echo # With more rows "range" changes to "ref_or_null"
-INSERT INTO t1 select seq,seq from seq_1_to_10;
+INSERT INTO t1 select seq,seq from seq_3_to_10;
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 90b60c6023c..c4258cfe057 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -3601,7 +3601,7 @@ SELECT * FROM t1
WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9)
ORDER BY pk LIMIT 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 index a,b PRIMARY 4 NULL 75 100.00 Using where
+1 SIMPLE t1 index a,b PRIMARY 4 NULL 73 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` between 9 and 10 or `test`.`t1`.`a` is null) and (`test`.`t1`.`b` between 9 and 10 or `test`.`t1`.`b` = 9) order by `test`.`t1`.`pk` limit 1
ANALYZE
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 780cc8f5100..3797de29935 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -334,7 +334,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 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
+2 MATERIALIZED lineitem index NULL i_l_orderkey_quantity 13 NULL 6005 100.00 Using index
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
@@ -368,7 +368,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 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
+2 MATERIALIZED lineitem index NULL i_l_orderkey_quantity 13 NULL 6005 100.00 Using index
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
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
index 7dbbd9079b5..cc25045767a 100644
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -1407,13 +1407,13 @@ EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
id 1
select_type SIMPLE
table t2
-type ref
+type index
possible_keys bkey
-key bkey
-key_len 5
-ref const
+key PRIMARY
+key_len 4
+ref NULL
rows 16
-Extra Using where; Using index; Using filesort
+Extra Using where
SELECT * FROM t2 WHERE b=1 ORDER BY a;
a b c
1 1 1
diff --git a/mysql-test/suite/innodb/r/partition_locking.result b/mysql-test/suite/innodb/r/partition_locking.result
index f25b8a15a24..b5e96f965b3 100644
--- a/mysql-test/suite/innodb/r/partition_locking.result
+++ b/mysql-test/suite/innodb/r/partition_locking.result
@@ -148,7 +148,7 @@ a b c d e
03 03 343 7 03_03_343
03 06 343 8 03_06_343
03 07 343 9 03_07_343
-SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED;
+SELECT a,count(b) FROM t1 force index (a) GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED;
a count(b)
01 5
03 3
diff --git a/mysql-test/suite/innodb/t/partition_locking.test b/mysql-test/suite/innodb/t/partition_locking.test
index 13457c1d9be..c4e6bff9bbb 100644
--- a/mysql-test/suite/innodb/t/partition_locking.test
+++ b/mysql-test/suite/innodb/t/partition_locking.test
@@ -104,7 +104,7 @@ SELECT * FROM t1 LOCK IN SHARE MODE;
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1 LOCK IN SHARE MODE NOWAIT;
SELECT * FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
-SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED;
+SELECT a,count(b) FROM t1 force index (a) GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED;
SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d
LOCK IN SHARE MODE SKIP LOCKED;
SELECT d,a,b,c FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
diff --git a/mysql-test/suite/innodb_gis/r/create_spatial_index.result b/mysql-test/suite/innodb_gis/r/create_spatial_index.result
index d3c69294c10..86634acaa01 100644
--- a/mysql-test/suite/innodb_gis/r/create_spatial_index.result
+++ b/mysql-test/suite/innodb_gis/r/create_spatial_index.result
@@ -57,6 +57,7 @@ ANALYZE TABLE tab;
Table Op Msg_type Msg_text
test.tab analyze status Engine-independent statistics collected
test.tab analyze status OK
+# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -85,6 +86,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -99,6 +101,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -127,6 +130,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -149,6 +153,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where
+# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -166,7 +171,7 @@ id select_type table type possible_keys key key_len ref rows Extra
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
+1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
@@ -194,6 +199,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -222,10 +228,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where
+# Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
+1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
@@ -250,6 +257,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -278,6 +286,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -300,6 +309,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where
+# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -314,10 +324,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
+1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
@@ -345,6 +356,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -359,10 +371,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
+1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
@@ -373,6 +386,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
+# Test with Procedure
CREATE PROCEDURE proc_wl6968()
BEGIN
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
@@ -388,6 +402,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the Delete & Update
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
@@ -486,6 +501,7 @@ ANALYZE TABLE tab;
Table Op Msg_type Msg_text
test.tab analyze status Engine-independent statistics collected
test.tab analyze status OK
+# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -514,6 +530,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -528,6 +545,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -556,6 +574,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -578,6 +597,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where
+# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -592,6 +612,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -623,6 +644,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -651,6 +673,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where
+# Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -679,6 +702,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -707,6 +731,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -729,6 +754,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where
+# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -743,6 +769,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -774,6 +801,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -788,6 +816,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -802,6 +831,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
+# Test with Procedure
CREATE PROCEDURE proc_wl6968()
BEGIN
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
@@ -817,6 +847,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the Delete & Update
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
@@ -904,6 +935,7 @@ ANALYZE TABLE tab;
Table Op Msg_type Msg_text
test.tab analyze status Engine-independent statistics collected
test.tab analyze status OK
+# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -932,6 +964,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -946,6 +979,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -974,6 +1008,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -996,6 +1031,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where
+# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1010,10 +1046,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
+1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
@@ -1041,6 +1078,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1069,10 +1107,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where
+# Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
+1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
@@ -1097,6 +1136,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1125,6 +1165,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1147,6 +1188,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where
+# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1161,10 +1203,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
+1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
@@ -1192,6 +1235,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1206,10 +1250,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
+# Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
+1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
@@ -1220,6 +1265,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
+# Test the Delete & Update
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
@@ -1243,6 +1289,7 @@ CHECK TABLE tab;
Table Op Msg_type Msg_text
test.tab check status OK
DROP TABLE tab;
+# Test check constraint on spatial column
CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB;
ERROR HY000: Illegal parameter data types point and int for operation '>'
CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(CAST(c1 AS BINARY) > 0) ) ENGINE=InnoDB;
diff --git a/mysql-test/suite/innodb_gis/t/create_spatial_index.test b/mysql-test/suite/innodb_gis/t/create_spatial_index.test
index 5278292b56c..cdf317503d1 100644
--- a/mysql-test/suite/innodb_gis/t/create_spatial_index.test
+++ b/mysql-test/suite/innodb_gis/t/create_spatial_index.test
@@ -94,7 +94,7 @@ ANALYZE TABLE tab;
# Check the spatial relationship between 2 GIS shapes
-# Test the MBRContains
+--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
@@ -117,7 +117,7 @@ WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
-# Test the MBRWithin
+--echo # Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
@@ -129,7 +129,7 @@ WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
-# Test the ST_Crosses
+--echo # Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
@@ -152,7 +152,7 @@ WHERE ST_Crosses(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
-# Test the MBRDisjoint
+--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
@@ -164,7 +164,7 @@ WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
-# Test the MBREquals
+--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
@@ -176,7 +176,6 @@ WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
-# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
@@ -199,7 +198,7 @@ WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
-# Test the Overelaps
+--echo # Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
@@ -222,7 +221,7 @@ WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
-# Test the ST_Touches
+--echo # Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
@@ -245,7 +244,7 @@ WHERE ST_Touches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
-# Test the MBRContains
+--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
@@ -268,7 +267,7 @@ WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
-# Test the MBRDisjoint
+--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
@@ -280,7 +279,7 @@ WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
-# Test the MBREquals
+--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
@@ -292,7 +291,7 @@ WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
-# Test the MBRintersects
+--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
@@ -315,7 +314,7 @@ WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
-# Test the MBROverelaps
+--echo # Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
@@ -327,7 +326,7 @@ WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
-# Test the MBRTouches
+--echo # Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
@@ -339,7 +338,7 @@ WHERE MBRTouches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
-# Test with Procedure
+--echo # Test with Procedure
delimiter |;
CREATE PROCEDURE proc_wl6968()
@@ -357,7 +356,7 @@ delimiter ;|
CALL proc_wl6968();
-# Test the Delete & Update
+--echo # Test the Delete & Update
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
@@ -478,7 +477,7 @@ ANALYZE TABLE tab;
# Check the spatial relationship between 2 GIS shapes
-# Test the MBRContains
+--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
@@ -501,7 +500,7 @@ WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
-# Test the MBRWithin
+--echo # Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
@@ -513,7 +512,7 @@ WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
-# Test the ST_Crosses
+--echo # Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
@@ -536,7 +535,7 @@ WHERE ST_Crosses(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
-# Test the MBRDisjoint
+--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
@@ -548,7 +547,7 @@ WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
-# Test the MBREquals
+--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
@@ -560,7 +559,7 @@ WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
-# Test the MBRintersects
+--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
@@ -583,7 +582,7 @@ WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
-# Test the Overelaps
+--echo # Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
@@ -606,7 +605,7 @@ WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
-# Test the ST_Touches
+--echo # Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
@@ -629,7 +628,7 @@ WHERE ST_Touches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
-# Test the MBRContains
+--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
@@ -652,7 +651,7 @@ WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
-# Test the MBRDisjoint
+--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
@@ -664,7 +663,7 @@ WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
-# Test the MBREquals
+--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
@@ -676,7 +675,7 @@ WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
-# Test the MBRintersects
+--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
@@ -699,7 +698,7 @@ WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
-# Test the MBROverelaps
+--echo # Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
@@ -711,7 +710,7 @@ WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
-# Test the MBRTouches
+--echo # Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
@@ -723,7 +722,7 @@ WHERE MBRTouches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
-# Test with Procedure
+--echo # Test with Procedure
delimiter |;
CREATE PROCEDURE proc_wl6968()
@@ -741,7 +740,7 @@ delimiter ;|
CALL proc_wl6968();
-# Test the Delete & Update
+--echo # Test the Delete & Update
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
@@ -850,7 +849,7 @@ ANALYZE TABLE tab;
# Check the spatial relationship between 2 GIS shapes
-# Test the MBRContains
+--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
@@ -873,7 +872,7 @@ WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
-# Test the MBRWithin
+--echo # Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
@@ -885,7 +884,7 @@ WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
-# Test the ST_Crosses
+--echo # Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
@@ -908,7 +907,7 @@ WHERE ST_Crosses(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
-# Test the MBRDisjoint
+--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
@@ -920,7 +919,7 @@ WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
-# Test the MBREquals
+--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
@@ -932,7 +931,7 @@ WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
-# Test the MBRintersects
+--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
@@ -955,7 +954,7 @@ WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
-# Test the Overelaps
+--echo # Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
@@ -978,7 +977,7 @@ WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
-# Test the ST_Touches
+--echo # Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
@@ -1001,7 +1000,7 @@ WHERE ST_Touches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
-# Test the MBRContains
+--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
@@ -1024,7 +1023,7 @@ WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
-# Test the MBRDisjoint
+--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
@@ -1036,7 +1035,7 @@ WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
-# Test the MBREquals
+--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
@@ -1048,7 +1047,7 @@ WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
-# Test the MBRintersects
+--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
@@ -1071,7 +1070,7 @@ WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
-# Test the MBROverelaps
+--echo # Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
@@ -1083,7 +1082,7 @@ WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
-# Test the MBRTouches
+--echo # Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
@@ -1095,7 +1094,7 @@ WHERE MBRTouches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
-# Test the Delete & Update
+--echo # Test the Delete & Update
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
@@ -1124,7 +1123,7 @@ DROP TABLE tab;
# End of Testcase compress table with Auto_increment
-# Test check constraint on spatial column
+--echo # Test check constraint on spatial column
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB;
CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(CAST(c1 AS BINARY) > 0) ) ENGINE=InnoDB;
diff --git a/sql/filesort_utils.cc b/sql/filesort_utils.cc
index 4a75817add4..854033cc8d8 100644
--- a/sql/filesort_utils.cc
+++ b/sql/filesort_utils.cc
@@ -28,7 +28,8 @@ const LEX_CSTRING filesort_names[]=
{ STRING_WITH_LEN("priority_queue with addon fields")},
{ STRING_WITH_LEN("priority_queue with row lookup")},
{ STRING_WITH_LEN("merge_sort with addon fields")},
- { STRING_WITH_LEN("merge_sort with row lookup)")}
+ { STRING_WITH_LEN("merge_sort with row lookup)")},
+ { STRING_WITH_LEN("Error while computing filesort cost")}
};
/*
@@ -447,7 +448,7 @@ double cost_of_filesort(TABLE *table, ORDER *order_by, ha_rows rows_to_read,
for (ORDER *ptr= order_by; ptr ; ptr= ptr->next)
{
Item_field *field= (Item_field*) (*ptr->item)->real_item();
- size_t length= get_sort_length(table->in_use, field);
+ size_t length= get_sort_length(thd, field);
set_if_smaller(length, thd->variables.max_sort_length);
sort_len+= (uint) length;
}
diff --git a/sql/filesort_utils.h b/sql/filesort_utils.h
index 1e1ddee06c0..b97fc4632c5 100644
--- a/sql/filesort_utils.h
+++ b/sql/filesort_utils.h
@@ -77,9 +77,8 @@ enum sort_type
MERGE_SORT_ALL_FIELDS,
MERGE_SORT_ORDER_BY_FIELDS,
- FINAL_SORT_TYPE,
-
- NO_SORT_POSSIBLE_OUT_OF_MEM,
+ NO_SORT_POSSIBLE_OUT_OF_MEM, /* In case of errors */
+ FINAL_SORT_TYPE= NO_SORT_POSSIBLE_OUT_OF_MEM
};
struct Sort_costs
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index e5f10e63249..23463eff2ca 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -100,6 +100,7 @@
#define crash_if_first_double_is_bigger(A,B) DBUG_ASSERT(((A) == 0.0 && (B) == 0.0) || (A)/(B) < 1.0000001)
+#define double_to_rows(A) ((A) >= ((double)HA_POS_ERROR) ? HA_POS_ERROR : (ha_rows) (A))
/* Cost for reading a row through an index */
struct INDEX_READ_COST
@@ -25124,7 +25125,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT)
{
/*
- we set ref_key=MAX_KEY instead of -1, because test_if_cheaper ordering
+ we set ref_key=MAX_KEY instead of -1, because test_if_cheaper_ordering()
assumes that "ref_key==-1" means doing full index scan.
(This is not very straightforward and we got into this situation for
historical reasons. Should be fixed at some point).
@@ -25313,10 +25314,10 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
}
order_direction= best_key_direction;
/*
- saved_best_key_parts is actual number of used keyparts found by the
- test_if_order_by_key function. It could differ from keyinfo->user_defined_key_parts,
- thus we have to restore it in case of desc order as it affects
- QUICK_SELECT_DESC behaviour.
+ saved_best_key_parts is actual number of used keyparts found by
+ the test_if_order_by_key function. It could differ from
+ keyinfo->user_defined_key_parts, thus we have to restore it in
+ case of desc order as it affects QUICK_SELECT_DESC behaviour.
*/
used_key_parts= (order_direction == -1) ?
saved_best_key_parts : best_key_parts;
@@ -25629,6 +25630,7 @@ create_sort_index(THD *thd, JOIN *join, JOIN_TAB *tab, Filesort *fsort)
}
else
{
+ fsort->own_select= false;
DBUG_ASSERT(tab->type == JT_REF || tab->type == JT_EQ_REF);
// Update ref value
if (unlikely(cp_buffer_from_ref(thd, table, &tab->ref) &&
@@ -29926,122 +29928,81 @@ void JOIN::cache_const_exprs()
/*
- Get the cost of using index keynr to read #LIMIT matching rows
+ Get the cost of using index keynr to read #LIMIT matching rows by calling
+ ha_index_next() repeatedly (either with index scan, quick or 'ref')
@detail
- If there is a quick select, we try to use it.
- - if there is a ref(const) access, we try to use it, too.
- - quick and ref(const) use different cost formulas, so if both are possible
- we should make a cost-based choice.
+ - If there is no quick select return the full cost from
+ cost_for_index_read() (Doing a full scan with up to 'limit' records)
+
+ @param pos Result from best_acccess_path(). Is NULL for
+ single-table UPDATE/DELETE
+ @param table Table to be sorted
+ @param keynr Which index to use
+ @param rows_limit How many rows we want to read.
+ This may be different than what was in the original
+ LIMIT the caller has included fanouts and extra
+ rows needed for handling GROUP BY.
+ @param rows_to_scan Number of rows to scan if there is no range.
+ @param read_cost Full cost, including cost of WHERE.
+ @param read_rows Number of rows that needs to be read
- rows_limit is the number of rows we would need to read when using a full
- index scan. This is generally higher than the N from "LIMIT N" clause,
- because there's a WHERE condition (a part of which is used to construct a
- range access we are considering using here)
-
- @param tab JOIN_TAB with table access (is NULL for single-table
- UPDATE/DELETE)
- @param rows_limit See explanation above
- @param read_time OUT Cost of reading using quick or ref(const) access.
+ @return
+ 0 No possible range scan, cost is for index scan
+ 1 Range scan should be used
+ For the moment we don't take selectivity of the WHERE clause into
+ account when calculating the number of rows we have to read
+ (except what we get from quick select).
- @return
- true There was a possible quick or ref access, its cost is in the OUT
- parameters.
- false No quick or ref(const) possible (and so, the caller will attempt
- to use a full index scan on this index).
+ The cost is calculated the following way:
+ (The selectivity is there to take into account the increased number of
+ rows that we have to read to find LIMIT matching rows)
*/
-static bool get_range_limit_read_cost(const JOIN_TAB *tab,
+static bool get_range_limit_read_cost(const POSITION *pos,
const TABLE *table,
- ha_rows table_records,
uint keynr,
ha_rows rows_limit,
- double *read_time,
+ ha_rows rows_to_scan,
+ double *read_cost,
double *read_rows)
{
- bool res= false;
- /*
- We need to adjust the estimates if we had a quick select (or ref(const)) on
- index keynr.
- */
if (table->opt_range_keys.is_set(keynr))
{
/*
Start from quick select's rows and cost. These are always cheaper than
full index scan/cost.
*/
- double best_rows= (double) table->opt_range[keynr].rows;
- double best_cost= (double) table->opt_range[keynr].fetch_cost;
+ double best_rows, range_rows;
+ double range_cost= (double) table->opt_range[keynr].fetch_cost;
+ best_rows= range_rows= (double) table->opt_range[keynr].rows;
- /*
- Check if ref(const) access was possible on this index.
- */
- if (tab)
+ if (pos)
{
- key_part_map map= 1;
- uint kp;
- /* Find how many key parts would be used by ref(const) */
- for (kp=0; kp < MAX_REF_PARTS; map=map << 1, kp++)
- {
- if (!(table->const_key_parts[keynr] & map))
- break;
- }
-
- if (kp > 0)
- {
- double ref_rows;
- /*
- Two possible cases:
- 1. ref(const) uses the same #key parts as range access.
- 2. ref(const) uses fewer key parts, becasue there is a
- range_cond(key_part+1).
- */
- if (kp == table->opt_range[keynr].key_parts)
- ref_rows= best_rows;
- else
- ref_rows= table->key_info[keynr].actual_rec_per_key(kp-1);
-
- if (ref_rows > 0)
- {
- INDEX_READ_COST cost= cost_for_index_read(tab->join->thd, table,
- keynr,
- (ha_rows)ref_rows,
- (ha_rows) tab->worst_seeks);
- if (cost.read_cost < best_cost)
- {
- best_cost= cost.read_cost;
- best_rows= ref_rows;
- }
- }
- }
- }
-
- /*
- Consider an example:
-
- SELECT *
- FROM t1
- WHERE key1 BETWEEN 10 AND 20 AND col2='foo'
- ORDER BY key1 LIMIT 10
-
- If we were using a full index scan on key1, we would need to read this
- many rows to get 10 matches:
+ /*
+ Take into count table selectivity as the number of accepted
+ rows for this table will be 'records_out'.
- 10 / selectivity(key1 BETWEEN 10 AND 20 AND col2='foo')
+ For example:
+ key1 BETWEEN 10 AND 1000 AND key2 BETWEEN 10 AND 20
- This is the number we get in rows_limit.
- But we intend to use range access on key1. The rows returned by quick
- select will satisfy the range part of the condition,
- "key1 BETWEEN 10 and 20". We will still need to filter them with
- the remainder condition, (col2='foo').
+ If we are trying to do an ORDER BY on key1, we have to take into
+ account that using key2 we have to examine much fewer rows.
+ */
+ best_rows= pos->records_out; // Best rows with any key/keys
+ double cond_selectivity= best_rows / range_rows;
+ DBUG_ASSERT(cond_selectivity <= 1.0);
- The selectivity of the range access is (best_rows/table_records). We need
- to discount it from the rows_limit:
- */
- double rows_limit_for_quick= rows_limit * (best_rows / table_records);
+ /*
+ We have to examine more rows in the proportion to the selectivity of the
+ the table
+ */
+ rows_limit= rows_limit / cond_selectivity;
+ }
- if (best_rows > rows_limit_for_quick)
+ if (best_rows > rows_limit)
{
/*
LIMIT clause specifies that we will need to read fewer records than
@@ -30050,14 +30011,36 @@ static bool get_range_limit_read_cost(const JOIN_TAB *tab,
only need 1/3rd of records, it will cost us 1/3rd of quick select's
read time)
*/
- best_cost *= rows_limit_for_quick / best_rows;
- best_rows = rows_limit_for_quick;
+ range_cost*= rows_limit / best_rows;
+ range_rows= rows_limit;
}
- *read_time= best_cost + best_rows * WHERE_COST_THD(table->in_use);
- *read_rows= best_rows;
- res= true;
+ *read_cost= range_cost + range_rows * WHERE_COST_THD(table->in_use);
+ *read_rows= range_rows;
+ return 1;
}
- return res;
+
+ /*
+ Calculate the number of rows we have to check if we are
+ doing a full index scan (as a suitabe range scan was not available).
+
+ We assume that each of the tested indexes is not correlated
+ with ref_key. Thus, to select first N records we have to scan
+ N/selectivity(ref_key) index entries.
+ selectivity(ref_key) = #scanned_records/#table_records =
+ refkey_rows_estimate/table_records.
+ In any case we can't select more than #table_records.
+ N/(refkey_rows_estimate/table_records) > table_records
+ <=> N > refkey_rows_estimate.
+ */
+ INDEX_READ_COST cost= cost_for_index_read(table->in_use, table, keynr,
+ rows_to_scan,
+ pos ?
+ (ha_rows) pos->table->worst_seeks :
+ HA_ROWS_MAX);
+ *read_cost= (cost.read_cost +
+ rows_to_scan * WHERE_COST_THD(table->in_use));
+ *read_rows= rows_to_scan;
+ return 0;
}
@@ -30079,7 +30062,8 @@ static bool get_range_limit_read_cost(const JOIN_TAB *tab,
@param [out] new_key Key number if success, otherwise undefined
@param [out] new_key_direction Return -1 (reverse) or +1 if success,
otherwise undefined
- @param [out] new_select_limit Return adjusted LIMIT
+ @param [out] new_select_limit Estimate of the number of rows we have
+ to read find 'select_limit' rows.
@param [out] new_used_key_parts NULL by default, otherwise return number
of new_key prefix columns if success
or undefined if the function fails
@@ -30110,25 +30094,41 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
It may be the case if ORDER/GROUP BY is used with LIMIT.
*/
ha_rows best_select_limit= HA_POS_ERROR;
- JOIN *join= tab ? tab->join : NULL;
+ JOIN *join;
uint nr;
key_map keys;
- uint best_key_parts= 0;
int best_key_direction= 0;
- ha_rows best_records= 0;
- double read_time;
+ double read_time, filesort_cost;
+ enum sort_type filesort_type;
int best_key= -1;
- bool is_best_covering= FALSE;
- double fanout= 1;
+ double fanout;
ha_rows table_records= table->stat_records();
- bool group= join && join->group && order == join->group_list;
- ha_rows refkey_rows_estimate= table->opt_range_condition_rows;
+ bool group;
const bool has_limit= (select_limit_arg != HA_POS_ERROR);
- THD* thd= join ? join->thd : table->in_use;
-
+ THD *thd= table->in_use;
+ POSITION *position;
+ ha_rows rows_estimate, refkey_rows_estimate;
Json_writer_object trace_wrapper(thd);
Json_writer_object trace_cheaper_ordering(
thd, "reconsidering_access_paths_for_index_ordering");
+
+ if (tab)
+ {
+ join= tab->join;
+ position= &join->best_positions[tab- join->join_tab];
+ group=join->group && order == join->group_list;
+ /* Take into account that records_out can be < 1.0 in case of GROUP BY */
+ rows_estimate= double_to_rows(position->records_out+0.5);
+ set_if_bigger(rows_estimate, 1);
+ refkey_rows_estimate= rows_estimate;
+ }
+ else
+ {
+ join= NULL;
+ position= 0;
+ refkey_rows_estimate= rows_estimate= table_records;
+ group= 0;
+ }
trace_cheaper_ordering.add("clause", group ? "GROUP BY" : "ORDER BY");
/*
@@ -30154,25 +30154,32 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
else
keys= usable_keys;
- if (join)
+
+ if (join) // True if SELECT
{
- uint tablenr= (uint)(tab - join->join_tab);
- read_time= join->best_positions[tablenr].read_time;
- for (uint i= tablenr+1; i < join->table_count; i++)
+ uint nr= (uint) (tab - join->join_tab);
+ fanout= 1.0;
+ if (nr != join->table_count - 1) // If not last table
+ fanout= (join->join_record_count / position->records_out);
+ else
{
- fanout*= join->best_positions[i].records_read; // fanout is always >= 1
- // But selectivity is =< 1 :
- fanout*= join->best_positions[i].cond_selectivity;
+ /* Only one table. Limit cannot be bigger than table_records */
+ set_if_smaller(select_limit_arg, table_records);
}
+ read_time= position->read_time;
}
else
- read_time= table->file->ha_scan_and_compare_time(table_records);
+ {
+ /* Probably an update or delete. Assume we will do a full table scan */
+ fanout= 1.0;
+ read_time= table->file->ha_scan_and_compare_time(rows_estimate);
+ set_if_smaller(select_limit_arg, table_records);
+ }
+
+ filesort_cost= cost_of_filesort(table, order, rows_estimate,
+ select_limit_arg, &filesort_type);
+ read_time+= filesort_cost;
- trace_cheaper_ordering.add("fanout", fanout);
- /*
- TODO: add cost of sorting here.
- */
- trace_cheaper_ordering.add("read_time", read_time);
/*
Calculate the selectivity of the ref_key for REF_ACCESS. For
RANGE_ACCESS we use table->opt_range_condition_rows.
@@ -30197,18 +30204,28 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
set_if_bigger(refkey_rows_estimate, 1);
}
- if (tab)
- trace_cheaper_ordering.add_table_name(tab);
- else
- trace_cheaper_ordering.add_table_name(table);
- trace_cheaper_ordering.add("rows_estimation", refkey_rows_estimate);
+ if (unlikely(thd->trace_started()))
+ {
+ if (tab)
+ trace_cheaper_ordering.add_table_name(tab);
+ else
+ trace_cheaper_ordering.add_table_name(table);
+ trace_cheaper_ordering
+ .add("rows_estimation", rows_estimate)
+ .add("read_cost", read_time)
+ .add("filesort_cost", filesort_cost)
+ .add("filesort_type", filesort_names[filesort_type].str)
+ .add("fanout", fanout);
+ }
Json_writer_array possible_keys(thd,"possible_keys");
for (nr=0; nr < table->s->keys ; nr++)
{
int direction;
ha_rows select_limit= select_limit_arg;
+ ha_rows estimated_rows_to_scan;
uint used_key_parts= 0;
+ double range_cost, range_rows;
Json_writer_object possible_key(thd);
possible_key.add("index", table->key_info[nr].name);
@@ -30238,14 +30255,11 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
queries too.
*/
if (is_covering ||
- select_limit != HA_POS_ERROR ||
+ has_limit ||
(ref_key < 0 && (group || table->force_index)))
{
double rec_per_key;
- double index_scan_time;
KEY *keyinfo= table->key_info+nr;
- if (select_limit == HA_POS_ERROR)
- select_limit= table_records;
if (group)
{
/*
@@ -30263,6 +30277,13 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
/* Take into account the selectivity of the used pk prefix */
if (used_pk_parts)
{
+ /*
+ TODO: This code need to be tested with debugger
+ - Why set rec_per_key to 1 if we don't have primary key data
+ or the full key is used ?
+ - If used_pk_parts == 1, we don't take into account that
+ the first primary key part could part of the current key.
+ */
KEY *pkinfo=tab->table->key_info+table->s->primary_key;
/*
If the values of of records per key for the prefixes
@@ -30294,7 +30315,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
rec_per_key/= pkinfo->actual_rec_per_key(i);
}
}
- }
+ }
}
set_if_bigger(rec_per_key, 1);
/*
@@ -30318,146 +30339,55 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
and as result we'll choose an index scan when using ref/range
access + filesort will be cheaper.
*/
- select_limit= (ha_rows) (select_limit < fanout ?
- 1 : select_limit/fanout);
-
- /*
- refkey_rows_estimate is E(#rows) produced by the table access
- strategy that was picked without regard to ORDER BY ... LIMIT.
-
- It will be used as the source of selectivity data.
- Use table->cond_selectivity as a better estimate which includes
- condition selectivity too.
- */
- {
- // we use MIN(...), because "Using LooseScan" queries have
- // cond_selectivity=1 while refkey_rows_estimate has a better
- // estimate.
- refkey_rows_estimate= MY_MIN(refkey_rows_estimate,
- ha_rows(table_records *
- table->cond_selectivity));
- }
-
- /*
- We assume that each of the tested indexes is not correlated
- with ref_key. Thus, to select first N records we have to scan
- N/selectivity(ref_key) index entries.
- selectivity(ref_key) = #scanned_records/#table_records =
- refkey_rows_estimate/table_records.
- In any case we can't select more than #table_records.
- N/(refkey_rows_estimate/table_records) > table_records
- <=> N > refkey_rows_estimate.
- */
+ select_limit= double_to_rows(select_limit/fanout);
+ set_if_bigger(select_limit, 1);
if (select_limit > refkey_rows_estimate)
- select_limit= table_records;
+ estimated_rows_to_scan= table_records;
else
- select_limit= (ha_rows) (select_limit *
- (double) table_records /
- refkey_rows_estimate);
- possible_key.add("updated_limit", select_limit);
- rec_per_key= keyinfo->actual_rec_per_key(keyinfo->user_defined_key_parts-1);
- set_if_bigger(rec_per_key, 1);
-#ifndef OLD_CODE
+ estimated_rows_to_scan= (ha_rows) (select_limit *
+ (double) table_records /
+ (double) refkey_rows_estimate);
+
+ bool range_scan= get_range_limit_read_cost(tab ? position : 0,
+ table,
+ nr,
+ select_limit,
+ estimated_rows_to_scan,
+ &range_cost,
+ &range_rows);
+ if (unlikely(possible_key.trace_started()))
{
- INDEX_READ_COST cost= cost_for_index_read(table->in_use, table, nr,
- select_limit,
- tab ?
- (ha_rows) tab->worst_seeks :
- HA_ROWS_MAX);
- index_scan_time= (cost.read_cost +
- select_limit * WHERE_COST_THD(thd));
+ possible_key
+ .add("rows_to_examine", range_rows)
+ .add("range_scan", range_scan)
+ .add("scan_cost", range_cost);
}
-#else
+
/*
- Here we take into account the fact that rows are
- accessed in sequences rec_per_key records in each.
- Rows in such a sequence are supposed to be ordered
- by rowid/primary key. When reading the data
- in a sequence we'll touch not more pages than the
- table file contains.
+ We will try use the key if:
+ - If there is no ref key and no usable keys has yet been found and
+ there is either a group by or a FORCE_INDEX
+ - If the new cost is better than read_time
*/
- index_scan_time= (select_limit/rec_per_key *
- MY_MIN(rec_per_key, table->file->ha_scan_time()));
-#endif
- possible_key.add("index_scan_cost", index_scan_time);
- double range_scan_time, range_rows;
- if (get_range_limit_read_cost(tab, table, table_records, nr,
- select_limit,
- &range_scan_time,
- &range_rows))
- {
- possible_key.add("range_scan_cost", range_scan_time);
- if (range_scan_time < index_scan_time)
- index_scan_time= range_scan_time;
- }
-
- if ((ref_key < 0 && (group || table->force_index || is_covering)) ||
- index_scan_time < read_time)
+ if (((table->force_index || group) && best_key < 0 && ref_key < 0) ||
+ range_cost < read_time)
{
- ha_rows quick_records= table_records;
- ha_rows refkey_select_limit= (ref_key >= 0 &&
- !is_hash_join_key_no(ref_key) &&
- table->covering_keys.is_set(ref_key)) ?
- refkey_rows_estimate :
- HA_POS_ERROR;
- if (is_best_covering && !is_covering)
- {
- if (unlikely(possible_key.trace_started()))
- possible_key.
- add("chosen", false).
- add("cause", "covering index already found");
- continue;
- }
-
- if (is_covering && refkey_select_limit < select_limit)
- {
- if (unlikely(possible_key.trace_started()))
- possible_key.
- add("chosen", false).
- add("cause", "ref estimates better");
- continue;
- }
- if (table->opt_range_keys.is_set(nr))
- quick_records= table->opt_range[nr].rows;
- possible_key.add("rows", quick_records);
- if (best_key < 0 ||
- (select_limit <= MY_MIN(quick_records,best_records) ?
- keyinfo->user_defined_key_parts < best_key_parts :
- quick_records < best_records) ||
- (!is_best_covering && is_covering))
- {
- possible_key.add("chosen", true);
- best_key= nr;
- best_key_parts= keyinfo->user_defined_key_parts;
- if (saved_best_key_parts)
- *saved_best_key_parts= used_key_parts;
- best_records= quick_records;
- is_best_covering= is_covering;
- best_key_direction= direction;
- best_select_limit= select_limit;
- }
- else
- {
- char const *cause;
- possible_key.add("chosen", false);
- if (is_covering)
- cause= "covering index already found";
- else
- {
- if (select_limit <= MY_MIN(quick_records,best_records))
- cause= "keyparts greater than the current best keyparts";
- else
- cause= "rows estimation greater";
- }
- possible_key.add("cause", cause);
- }
+ read_time= range_cost;
+ possible_key.add("chosen", true);
+ best_key= nr;
+ if (saved_best_key_parts)
+ *saved_best_key_parts= used_key_parts;
+ if (new_used_key_parts)
+ *new_used_key_parts= keyinfo->user_defined_key_parts;
+ best_key_direction= direction;
+ best_select_limit= estimated_rows_to_scan;
}
else if (unlikely(possible_key.trace_started()))
{
- possible_key.
- add("usable", false).
- add("cause", "cost");
+ possible_key
+ .add("usable", false)
+ .add("cause", "cost");
}
}
else if (unlikely(possible_key.trace_started()))
@@ -30490,8 +30420,6 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
*new_key= best_key;
*new_key_direction= best_key_direction;
*new_select_limit= has_limit ? best_select_limit : table_records;
- if (new_used_key_parts != NULL)
- *new_used_key_parts= best_key_parts;
DBUG_RETURN(TRUE);
}
View Lorry Controller Status