mysql> show create table tabl_; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tabl_ | CREATE TABLE `tabl_` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `a______` int(11) NOT NULL, `irrelevant___` bigint(20) NOT NULL, `c__________` timestamp NULL DEFAULT NULL, `d` decimal(8,1) NOT NULL, `b_________` tinyint(4) NOT NULL, PRIMARY KEY (`id`), KEY `idx_a_______c__________` (`a______`,`c__________`), KEY `idx_a______b_________c_________` (`a______`,`b_________`,`c__________`) ) ENGINE=InnoDB AUTO_INCREMENT=50207041306681 DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> analyze table db_______.tabl_; +-----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------+---------+----------+----------+ | db_______.tabl_ | analyze | status | OK | +-----------------+---------+----------+----------+ 1 row in set (2.54 sec) mysql> set optimizer_trace=1; Query OK, 0 rows affected (0.00 sec) mysql> explain SELECT d, UNIX_TIMESTAMP(c__________), id FROM db_______.tabl_ WHERE a______ = '44983' AND c__________ >= FROM_UNIXTIME('1396502959[2014-04-03 08:29:19]') AND c__________ < FROM_UNIXTIME('1411415531[2014-09-22 22:52:11]') ORDER BY c__________; +----+-------------+-------+------+---------------------------------------------------------+---------------------------------+---------+-------+--------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------------------------------------------+---------------------------------+---------+-------+--------+----------------------------------------------------+ | 1 | SIMPLE | tabl_ | ref | idx_a_______c__________,idx_a______b_________c_________ | idx_a______b_________c_________ | 4 | const | 749988 | Using index condition; Using where; Using filesort | +----+-------------+-------+------+---------------------------------------------------------+---------------------------------+---------+-------+--------+----------------------------------------------------+ 1 row in set (0.15 sec) mysql> select * from information_schema.optimizer_trace \G *************************** 1. row *************************** QUERY: explain SELECT d, UNIX_TIMESTAMP(c__________), id FROM db_______.tabl_ WHERE a______ = '44983' AND c__________ >= FROM_UNIXTIME('1396502959[2014-04-03 08:29:19]') AND c__________ < FROM_UNIXTIME('1411415531[2014-09-22 22:52:11]') ORDER BY c__________ TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `tabl_`.`d` AS `d`,unix_timestamp(`tabl_`.`c__________`) AS `UNIX_TIMESTAMP(c__________)`,`tabl_`.`id` AS `id` from `tabl_` where ((`tabl_`.`a______` = '44983') and (`tabl_`.`c__________` >= from_unixtime('1396502959[2014-04-03 08:29:19]')) and (`tabl_`.`c__________` < from_unixtime('1411415531[2014-09-22 22:52:11]'))) order by `tabl_`.`c__________`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`tabl_`.`a______` = '44983') and (`tabl_`.`c__________` >= from_unixtime('1396502959[2014-04-03 08:29:19]')) and (`tabl_`.`c__________` < from_unixtime('1411415531[2014-09-22 22:52:11]')))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`tabl_`.`a______` = '44983') and (`tabl_`.`c__________` >= from_unixtime('1396502959[2014-04-03 08:29:19]')) and (`tabl_`.`c__________` < from_unixtime('1411415531[2014-09-22 22:52:11]')))" }, { "transformation": "constant_propagation", "resulting_condition": "((`tabl_`.`a______` = '44983') and (`tabl_`.`c__________` >= from_unixtime('1396502959[2014-04-03 08:29:19]')) and (`tabl_`.`c__________` < from_unixtime('1411415531[2014-09-22 22:52:11]')))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`tabl_`.`a______` = '44983') and (`tabl_`.`c__________` >= from_unixtime('1396502959[2014-04-03 08:29:19]')) and (`tabl_`.`c__________` < from_unixtime('1411415531[2014-09-22 22:52:11]')))" } ] } }, { "table_dependencies": [ { "table": "`tabl_`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`tabl_`", "field": "a______", "equals": "'44983'", "null_rejecting": false }, { "table": "`tabl_`", "field": "a______", "equals": "'44983'", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`tabl_`", "range_analysis": { "table_scan": { "rows": 474631282, "cost": 9.68e7 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "idx_a_______c__________", "usable": true, "key_parts": [ "a______", "c__________", "id" ] }, { "index": "idx_a______b_________c_________", "usable": true, "key_parts": [ "a______", "b_________", "c__________", "id" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_a_______c__________", "ranges": [ "44983 <= a______ <= 44983 AND 2014-04-03 08:29:19 <= c__________ < 2014-09-22 22:52:11" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 792944, "cost": 951534, "chosen": true }, { "index": "idx_a______b_________c_________", "ranges": [ "44983 <= a______ <= 44983" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 749988, "cost": 899987, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "idx_a______b_________c_________", "rows": 749988, "ranges": [ "44983 <= a______ <= 44983" ] }, "rows_for_plan": 749988, "cost_for_plan": 899987, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tabl_`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idx_a_______c__________", "rows": 792944, "cost": 159589, "chosen": true }, { "access_type": "ref", "index": "idx_a______b_________c_________", "rows": 749988, "cost": 150998, "chosen": true }, { "access_type": "range", "cause": "heuristic_index_cheaper", "chosen": false } ] }, "cost_for_plan": 150998, "rows_for_plan": 749988, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`tabl_`.`a______` = '44983') and (`tabl_`.`c__________` >= from_unixtime('1396502959[2014-04-03 08:29:19]')) and (`tabl_`.`c__________` < from_unixtime('1411415531[2014-09-22 22:52:11]')))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`tabl_`", "attached": "((`tabl_`.`c__________` >= from_unixtime('1396502959[2014-04-03 08:29:19]')) and (`tabl_`.`c__________` < from_unixtime('1411415531[2014-09-22 22:52:11]')))" } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`tabl_`.`c__________`", "items": [ { "item": "`tabl_`.`c__________`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`tabl_`.`c__________`" } }, { "refine_plan": [ { "table": "`tabl_`", "pushed_index_condition": "((`tabl_`.`c__________` >= from_unixtime('1396502959[2014-04-03 08:29:19]')) and (`tabl_`.`c__________` < from_unixtime('1411415531[2014-09-22 22:52:11]')))", "table_condition_attached": null } ] }, { "added_back_ref_condition": "((`tabl_`.`a______` <=> '44983'))" }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "index_order_summary": { "table": "`tabl_`", "index_provides_order": false, "order_direction": "undefined", "index": "idx_a______b_________c_________", "plan_changed": false } } } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.01 sec) mysql> show index in tabl_; +-------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tabl_ | 0 | PRIMARY | 1 | id | A | 474632198 | NULL | NULL | | BTREE | | | | tabl_ | 1 | idx_a_______c__________ | 1 | a______ | A | 591073 | NULL | NULL | | BTREE | | | | tabl_ | 1 | idx_a_______c__________ | 2 | c__________ | A | 474632198 | NULL | NULL | YES | BTREE | | | | tabl_ | 1 | idx_a______b_________c_________ | 1 | a______ | A | 424916 | NULL | NULL | | BTREE | | | | tabl_ | 1 | idx_a______b_________c_________ | 2 | b_________ | A | 704202 | NULL | NULL | | BTREE | | | | tabl_ | 1 | idx_a______b_________c_________ | 3 | c__________ | A | 474632198 | NULL | NULL | YES | BTREE | | | +-------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.00 sec) mysql> explain SELECT d, UNIX_TIMESTAMP(c__________), id FROM db_______.tabl_ FORCE INDEX (idx_a_______c__________) WHERE a______ = '44983' AND c__________ >= FROM_UNIXTIME('1396502959[2014-04-03 08:29:19]') AND c__________ < FROM_UNIXTIME('1411415531[2014-09-22 22:52:11]') ORDER BY c__________; +----+-------------+-------+-------+-------------------------+-------------------------+---------+------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-------------------------+-------------------------+---------+------+--------+-----------------------+ | 1 | SIMPLE | tabl_ | range | idx_a_______c__________ | idx_a_______c__________ | 9 | NULL | 792944 | Using index condition | +----+-------------+-------+-------+-------------------------+-------------------------+---------+------+--------+-----------------------+ 1 row in set (0.00 sec)