summaryrefslogtreecommitdiff
path: root/mysql-test/main/index_intersect.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/index_intersect.result')
-rw-r--r--mysql-test/main/index_intersect.result274
1 files changed, 271 insertions, 3 deletions
diff --git a/mysql-test/main/index_intersect.result b/mysql-test/main/index_intersect.result
index 1f31636860f..9758425d98c 100644
--- a/mysql-test/main/index_intersect.result
+++ b/mysql-test/main/index_intersect.result
@@ -75,7 +75,7 @@ EXPLAIN
SELECT * FROM City
WHERE Name LIKE 'M%' AND Population > 300000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City index_merge Population,Name Name,Population 35,4 NULL # Using sort_intersect(Name,Population); Using where
+1 SIMPLE City range Population,Name Name 35 NULL # Using index condition; Using where
EXPLAIN
SELECT * FROM City
WHERE Name LIKE 'M%' AND Population > 7000000;
@@ -712,7 +712,275 @@ EXPLAIN
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City index_merge Population,Country,Name Name,Country 35,3 NULL # Using sort_intersect(Name,Country); Using where
+1 SIMPLE City range Population,Country,Name Name 35 NULL # Using index condition; Using where
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+EXPLAIN
+SELECT * FROM City
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%' {
+ "steps": [
+ {
+ "join_preparation": {
+ "select_id": 1,
+ "steps": [
+ {
+ "expanded_query": "select City.`ID` AS `ID`,City.`Name` AS `Name`,City.Country AS Country,City.Population AS Population from City where City.`Name` between 'G' and 'J' and City.Population > 500000 and City.Country like 'C%'"
+ }
+ ]
+ }
+ },
+ {
+ "join_optimization": {
+ "select_id": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "City.`Name` between 'G' and 'J' and City.Population > 500000 and City.Country like 'C%'",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "City.`Name` between 'G' and 'J' and City.Population > 500000 and City.Country like 'C%'"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "City.`Name` between 'G' and 'J' and City.Population > 500000 and City.Country like 'C%'"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "City.`Name` between 'G' and 'J' and City.Population > 500000 and City.Country like 'C%'"
+ }
+ ]
+ }
+ },
+ {
+ "table_dependencies": [
+ {
+ "table": "City",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": []
+ }
+ ]
+ },
+ {
+ "ref_optimizer_key_uses": []
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "City",
+ "range_analysis": {
+ "table_scan": {
+ "rows": 4079,
+ "cost": 0.659034395
+ },
+ "potential_range_indexes": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not applicable"
+ },
+ {
+ "index": "Population",
+ "usable": true,
+ "key_parts": ["Population"]
+ },
+ {
+ "index": "Country",
+ "usable": true,
+ "key_parts": ["Country"]
+ },
+ {
+ "index": "Name",
+ "usable": true,
+ "key_parts": ["Name"]
+ }
+ ],
+ "setup_range_conditions": [],
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "Population",
+ "ranges": ["(500000) < (Population)"],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 540,
+ "cost": 0.667779759,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "index": "Country",
+ "ranges": ["(C\0\0) <= (Country) <= (Cÿÿ)"],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 538,
+ "cost": 0.665312021,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "index": "Name",
+ "ranges": ["(G) <= (Name) <= (J)"],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 226,
+ "cost": 0.280344893,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect": {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_sort_intersect": {
+ "potential_index_scans": [
+ {
+ "index": "Population",
+ "cost": 0.058326157,
+ "chosen": true,
+ "cause": "first occurrence of index prefix"
+ },
+ {
+ "index": "Country",
+ "cost": 0.058113617,
+ "chosen": true,
+ "cause": "first occurrence of index prefix"
+ },
+ {
+ "index": "Name",
+ "cost": 0.024957377,
+ "chosen": true,
+ "cause": "first occurrence of index prefix"
+ }
+ ],
+ "selected_index_scans": [
+ {
+ "index": "Name",
+ "keyparts": ["Name"],
+ "rows": 226,
+ "filtered_records": 0
+ },
+ {
+ "index": "Country",
+ "keyparts": ["Country"],
+ "rows": 538,
+ "filtered_records": 0
+ },
+ {
+ "index": "Population",
+ "keyparts": ["Population"],
+ "rows": 540,
+ "filtered_records": 0
+ }
+ ]
+ },
+ "analyzing_index_merge_union": []
+ },
+ "group_index_range": {
+ "chosen": false,
+ "cause": "no group by or distinct"
+ },
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "Name",
+ "rows": 226,
+ "ranges": ["(G) <= (Name) <= (J)"]
+ },
+ "rows_for_plan": 226,
+ "cost_for_plan": 0.280344893,
+ "chosen": true
+ }
+ }
+ },
+ {
+ "selectivity_for_indexes": [
+ {
+ "index_name": "Name",
+ "selectivity_from_index": 0.055405737
+ },
+ {
+ "index_name": "Country",
+ "selectivity_from_index": 0.131895072
+ },
+ {
+ "index_name": "Population",
+ "selectivity_from_index": 0.132385389
+ }
+ ],
+ "selectivity_for_columns": [],
+ "cond_selectivity": 9.674385e-4
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [],
+ "table": "City",
+ "best_access_path": {
+ "plan_details": {
+ "record_count": 1
+ },
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "rows": 226,
+ "rows_after_filter": 226,
+ "rows_out": 3.946181571,
+ "cost": 0.280344893,
+ "chosen": true
+ }
+ ],
+ "chosen_access_method": {
+ "type": "range",
+ "rows_read": 226,
+ "rows_out": 3.946181571,
+ "cost": 0.280344893,
+ "uses_join_buffering": false
+ }
+ },
+ "rows_for_plan": 3.946181571,
+ "cost_for_plan": 0.280344893
+ }
+ ]
+ },
+ {
+ "best_join_order": ["City"],
+ "rows": 3.946181571,
+ "cost": 0.280344893
+ },
+ {
+ "substitute_best_equal": {
+ "condition": "WHERE",
+ "resulting_condition": "City.`Name` between 'G' and 'J' and City.Population > 500000 and City.Country like 'C%'"
+ }
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "attached_conditions_computation": [],
+ "attached_conditions_summary": [
+ {
+ "table": "City",
+ "attached": "City.`Name` between 'G' and 'J' and City.Population > 500000 and City.Country like 'C%'"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ },
+ {
+ "join_execution": {
+ "select_id": 1,
+ "steps": []
+ }
+ }
+ ]
+} 0 0
EXPLAIN
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%';
@@ -723,7 +991,7 @@ SELECT * FROM City
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City index_merge PRIMARY,Population,Country Population,Country 4,3 NULL # Using sort_intersect(Population,Country); Using where
+1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using index condition; Using where
SELECT * FROM City WHERE
Name LIKE 'C%' AND Population > 1000000;
ID Name Country Population
View Lorry Controller Status