mysql有个表比较大900多万行, 占用15G空间,每条记录100多个字段...
现需要进行 IN(5,10)查询, 并且需要对结果倒序,分页操作
即使where查询字段有索引, 运行以下语句耗时10秒

SELECT * FROM `order` 
WHERE `city_id` = 224 
AND `state` in (5,10) 
ORDER BY ctime desc 
LIMIT 0,12

于是按in 条件拆成两个select语句,然后union all ,最后排序分页,耗时0.7秒

SELECT * FROM `order`
WHERE `city_id` = 224 
AND`state` = 5 
UNION ALL
SELECT * FROM `order` 
WHERE `city_id` = 224 
AND `state` = 10 
ORDER BY ctime desc
LIMIT 0,12