| 1 |
SET optimizer_trace_max_mem_size=1048576; |
| 2 |
SET @@session.optimizer_trace="enabled=on"; |
| 3 |
# |
| 4 |
# BUG#18195373: OPTIMIZER TRACE SHOULD PRINT NAME OF |
| 5 |
# INDEX FOR RANGE ACCESS |
| 6 |
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); |
| 7 |
INSERT INTO t1 VALUES('Testing how', 'full-text search'), |
| 8 |
('is printed to the', 'optimizer trace.'), |
| 9 |
('We need test coverage','of that too'); |
| 10 |
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("coverage"); |
| 11 |
a b |
| 12 |
We need test coverage of that too |
| 13 |
SELECT * FROM information_schema.OPTIMIZER_TRACE; |
| 14 |
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES |
| 15 |
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("coverage") { |
| 16 |
"steps": [ |
| 17 |
{ |
| 18 |
"join_preparation": { |
| 19 |
"select#": 1, |
| 20 |
"steps": [ |
| 21 |
{ |
| 22 |
"expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (match `t1`.`a`,`t1`.`b` against ('coverage'))" |
| 23 |
} |
| 24 |
] |
| 25 |
} |
| 26 |
}, |
| 27 |
{ |
| 28 |
"join_optimization": { |
| 29 |
"select#": 1, |
| 30 |
"steps": [ |
| 31 |
{ |
| 32 |
"condition_processing": { |
| 33 |
"condition": "WHERE", |
| 34 |
"original_condition": "(match `t1`.`a`,`t1`.`b` against ('coverage'))", |
| 35 |
"steps": [ |
| 36 |
{ |
| 37 |
"transformation": "equality_propagation", |
| 38 |
"resulting_condition": "(match `t1`.`a`,`t1`.`b` against ('coverage'))" |
| 39 |
}, |
| 40 |
{ |
| 41 |
"transformation": "constant_propagation", |
| 42 |
"resulting_condition": "(match `t1`.`a`,`t1`.`b` against ('coverage'))" |
| 43 |
}, |
| 44 |
{ |
| 45 |
"transformation": "trivial_condition_removal", |
| 46 |
"resulting_condition": "(match `t1`.`a`,`t1`.`b` against ('coverage'))" |
| 47 |
} |
| 48 |
] |
| 49 |
} |
| 50 |
}, |
| 51 |
{ |
| 52 |
"substitute_generated_columns": { |
| 53 |
} |
| 54 |
}, |
| 55 |
{ |
| 56 |
"table_dependencies": [ |
| 57 |
{ |
| 58 |
"table": "`t1`", |
| 59 |
"row_may_be_null": false, |
| 60 |
"map_bit": 0, |
| 61 |
"depends_on_map_bits": [ |
| 62 |
] |
| 63 |
} |
| 64 |
] |
| 65 |
}, |
| 66 |
{ |
| 67 |
"ref_optimizer_key_uses": [ |
| 68 |
{ |
| 69 |
"table": "`t1`", |
| 70 |
"field": "<fulltext>", |
| 71 |
"equals": "(match `t1`.`a`,`t1`.`b` against ('coverage'))", |
| 72 |
"null_rejecting": false |
| 73 |
} |
| 74 |
] |
| 75 |
}, |
| 76 |
{ |
| 77 |
"rows_estimation": [ |
| 78 |
{ |
| 79 |
"table": "`t1`", |
| 80 |
"range_analysis": { |
| 81 |
"table_scan": { |
| 82 |
"rows": 3, |
| 83 |
"cost": 2.90732 |
| 84 |
}, |
| 85 |
"potential_range_indexes": [ |
| 86 |
{ |
| 87 |
"index": "a", |
| 88 |
"usable": false, |
| 89 |
"cause": "fulltext" |
| 90 |
} |
| 91 |
], |
| 92 |
"setup_range_conditions": [ |
| 93 |
], |
| 94 |
"group_index_range": { |
| 95 |
"chosen": false, |
| 96 |
"cause": "not_group_by_or_distinct" |
| 97 |
}, |
| 98 |
"skip_scan_range": { |
| 99 |
"chosen": false, |
| 100 |
"cause": "disjuntive_predicate_present" |
| 101 |
} |
| 102 |
} |
| 103 |
} |
| 104 |
] |
| 105 |
}, |
| 106 |
{ |
| 107 |
"considered_execution_plans": [ |
| 108 |
{ |
| 109 |
"plan_prefix": [ |
| 110 |
], |
| 111 |
"table": "`t1`", |
| 112 |
"best_access_path": { |
| 113 |
"considered_access_paths": [ |
| 114 |
{ |
| 115 |
"access_type": "fulltext", |
| 116 |
"index": "a", |
| 117 |
"rows": 1, |
| 118 |
"cost": 0.35, |
| 119 |
"chosen": true |
| 120 |
}, |
| 121 |
{ |
| 122 |
"access_type": "scan", |
| 123 |
"cost": 0.807324, |
| 124 |
"rows": 3, |
| 125 |
"chosen": false, |
| 126 |
"cause": "cost" |
| 127 |
} |
| 128 |
] |
| 129 |
}, |
| 130 |
"condition_filtering_pct": 100, |
| 131 |
"rows_for_plan": 1, |
| 132 |
"cost_for_plan": 0.35, |
| 133 |
"chosen": true |
| 134 |
} |
| 135 |
] |
| 136 |
}, |
| 137 |
{ |
| 138 |
"attaching_conditions_to_tables": { |
| 139 |
"original_condition": "(match `t1`.`a`,`t1`.`b` against ('coverage'))", |
| 140 |
"attached_conditions_computation": [ |
| 141 |
], |
| 142 |
"attached_conditions_summary": [ |
| 143 |
{ |
| 144 |
"table": "`t1`", |
| 145 |
"attached": "(match `t1`.`a`,`t1`.`b` against ('coverage'))" |
| 146 |
} |
| 147 |
] |
| 148 |
} |
| 149 |
}, |
| 150 |
{ |
| 151 |
"finalizing_table_conditions": [ |
| 152 |
{ |
| 153 |
"table": "`t1`", |
| 154 |
"original_table_condition": "(match `t1`.`a`,`t1`.`b` against ('coverage'))", |
| 155 |
"final_table_condition ": "(match `t1`.`a`,`t1`.`b` against ('coverage'))" |
| 156 |
} |
| 157 |
] |
| 158 |
}, |
| 159 |
{ |
| 160 |
"refine_plan": [ |
| 161 |
{ |
| 162 |
"table": "`t1`" |
| 163 |
} |
| 164 |
] |
| 165 |
} |
| 166 |
] |
| 167 |
} |
| 168 |
}, |
| 169 |
{ |
| 170 |
"join_execution": { |
| 171 |
"select#": 1, |
| 172 |
"steps": [ |
| 173 |
] |
| 174 |
} |
| 175 |
} |
| 176 |
] |
| 177 |
} 0 0 |
| 178 |
DROP TABLE t1; |