mysql> CREATE DATABASE test; Query OK, 1 row affected (0.01 sec) mysql> USE test; Database changed mysql> CREATE TABLE test.repro ( -> a char(20), -> b char(20), -> PRIMARY KEY (a, b(16)) -> ); Query OK, 0 rows affected (0.01 sec) mysql> -- The view "dba.generator_64k" simply returns a series of integers from 1 to 64,000. mysql> INSERT INTO test.repro (a, b) -> SELECT concat(left(md5(cast(n/1000 as signed)),19), '='), left(md5(n),20) from dba.generator_64k n; Query OK, 65536 rows affected (0.47 sec) Records: 65536 Duplicates: 0 Warnings: 0 mysql> SET optimizer_trace="enabled=on",end_markers_in_json=on; Query OK, 0 rows affected (0.00 sec) mysql> SET optimizer_trace_max_mem_size=10000000; Query OK, 0 rows affected (0.00 sec) mysql> -- Unexpected behavious "b" not in index lookup mysql> EXPLAIN ANALYZE SELECT count(*) FROM test.repro -> WHERE repro.a = 'eccbc87e4b5ce2fe283=' AND repro.b IN ('7a4bf9ba2bd774068ad5', '6fab6e3aa34248ec1e34')\G *************************** 1. row *************************** EXPLAIN: -> Aggregate: count(0) (cost=1.11 rows=1) (actual time=0.117..0.117 rows=1 loops=1) -> Filter: ((repro.a = 'eccbc87e4b5ce2fe283=') and (repro.b in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34'))) (cost=0.91 rows=2) (actual time=0.088..0.1 rows=2 loops=1) -> Index range scan on repro using PRIMARY over (a = 'eccbc87e4b5ce2fe283=' AND b = '6fab6e3aa34248ec') OR (a = 'eccbc87e4b5ce2fe283=' AND b = '7a4bf9ba2bd77406') (cost=0.91 rows=2) (actual time=0.0824..0.0928 rows=2 loops=1) 1 row in set (0.00 sec) mysql> SELECT * FROM information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: EXPLAIN ANALYZE SELECT count(*) FROM test.repro WHERE repro.a = 'eccbc87e4b5ce2fe283=' AND repro.b IN ('7a4bf9ba2bd774068ad5', '6fab6e3aa34248ec1e34') TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "IN_uses_bisection": true }, { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `repro` where ((`repro`.`a` = 'eccbc87e4b5ce2fe283=') and (`repro`.`b` in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34')))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`repro`.`a` = 'eccbc87e4b5ce2fe283=') and (`repro`.`b` in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34')))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`repro`.`b` in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34')) and multiple equal('eccbc87e4b5ce2fe283=', `repro`.`a`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`repro`.`b` in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34')) and multiple equal('eccbc87e4b5ce2fe283=', `repro`.`a`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`repro`.`b` in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34')) and multiple equal('eccbc87e4b5ce2fe283=', `repro`.`a`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`repro`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`repro`", "field": "a", "equals": "'eccbc87e4b5ce2fe283='", "null_rejecting": true } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`repro`", "range_analysis": { "table_scan": { "rows": 65536, "cost": 6555.95 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "a", "b" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "a = 'eccbc87e4b5ce2fe283=' AND b = '6fab6e3aa34248ec'", "a = 'eccbc87e4b5ce2fe283=' AND b = '7a4bf9ba2bd77406'" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 2, "cost": 0.71, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 2, "ranges": [ "a = 'eccbc87e4b5ce2fe283=' AND b = '6fab6e3aa34248ec'", "a = 'eccbc87e4b5ce2fe283=' AND b = '7a4bf9ba2bd77406'" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 2, "cost_for_plan": 0.71, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`repro`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "range_uses_more_keyparts" }, { "rows_to_scan": 2, "filtering_effect": [ ] /* filtering_effect */, "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "PRIMARY" } /* range_details */, "resulting_rows": 2, "cost": 0.91, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 2, "cost_for_plan": 0.91, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`repro`.`a` = 'eccbc87e4b5ce2fe283=') and (`repro`.`b` in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34')))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`repro`", "attached": "((`repro`.`a` = 'eccbc87e4b5ce2fe283=') and (`repro`.`b` in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34')))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ { "table": "`repro`", "original_table_condition": "((`repro`.`a` = 'eccbc87e4b5ce2fe283=') and (`repro`.`b` in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34')))", "final_table_condition ": "((`repro`.`a` = 'eccbc87e4b5ce2fe283=') and (`repro`.`b` in ('7a4bf9ba2bd774068ad5','6fab6e3aa34248ec1e34')))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`repro`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)