SQL
SELECT id AS history_id, serial_number, expiry_id, print_count, print_by
, repeated_print, expiry_type_id, expiry_type_name, tag_start_time AS print_time, created_time
, channel_id, expiry_duration, expiry_duration_unit_id, expiry_duration_unit_name, expiry_time
FROM pd_xq_expiry_print_history
WHERE store_id = 11023
AND stock_print_name LIKE concat('%', '嘻嘻', '%')
AND print_time BETWEEN '2021-06-12 00:00' AND '2021-07-12 23:59'
ORDER BY history_id DESC
limit 20;
索引
KEY `idx_store_id` (`store_id`),
KEY `idx_print_store` (`store_id`, `print_time`, `expiry_time`),
explain结果
优化
order by print_time 或者 强制索引
原因
order by id,mysql 的优化器会选择主键索引,但是 where 条件里又没有主键条件,导致全表扫描。
order by gmt_create,结合 where 条件里 gmt_create ,mysq 优化器会选择 gmt_create 索引,扫描的记录数少,效果更好。
详见 详见