|
Optimizer does a wrong cast from column string to number instead of constant number to string:
source sales_flat_order_dump.sql
|
analyze table sales_flat_order;
|
|
explain
|
SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` WHERE (`increment_id` >= 238900 AND `increment_id` <= 238907)
|
;
|
+------+-------------+------------+------+-----------------------------------+------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+-----------------------------------+------+---------+------+--------+-------------+
|
| 1 | SIMPLE | main_table | ALL | UNQ_SALES_FLAT_ORDER_INCREMENT_ID | NULL | NULL | NULL | 260559 | Using where |
|
+------+-------------+------------+------+-----------------------------------+------+---------+------+--------+-------------+
|
|
Execution time:
3 rows in set (1.467 sec)
explain
|
SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` WHERE (`increment_id` >= '238900' AND `increment_id` <= '238907')
|
;
|
+------+-------------+------------+-------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------+
|
| 1 | SIMPLE | main_table | range | UNQ_SALES_FLAT_ORDER_INCREMENT_ID | UNQ_SALES_FLAT_ORDER_INCREMENT_ID | 153 | NULL | 3 | Using index condition |
|
+------+-------------+------------+-------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------+
|
|
Execution time:
3 rows in set (0.002 sec)
This is reproducible at will. See example attached.
|
sales_flat_order_dump.sql.gz