建立联合索引 alter table xxx add index(is_delete,is_real,goods_i)
explain SELECT goods_id, goods_name, goods_type, goods_sn, shop_price, is_on_sale, is_best, is_new, is_hot, sort_order, goods_number, integral, (promote_price > 0 AND promote_start_date <= '1349942399' AND promote_end_date >= '1349942399') AS is_promote FROM `icmall`.`ecs_goods` AS g WHERE is_delete='0' AND is_real='1' ORDER BY goods_id DESC LIMIT 2111111,20 1 SIMPLE g ref delete_real_goods_id delete_real_goods_id 2 const,const 2302639 Using where
支持6楼,这两个字段只有0和1,选择性低,用索引相当于全表扫描select ..... from `icmall`.`ecs_goods` AS g join (select goods_id FROM `icmall`.`ecs_goods` WHERE is_delete='0' AND is_real='1' ORDER BY goods_id DESC LIMIT 2111111,20 ) as t using (goods_id)
1 SIMPLE g ref delete_real_goods_id delete_real_goods_id 2 const,const 2302639 Using where
你有没有可能获得上一页的id,通过id>的方式来处理分页?否则有这么多IO,时间耗费肯定会比较高的
还是用6楼的方法吧, 改表一下思路吧.
能写个大概的SQL语句做个参考吗。呵呵。谢谢
(select goods_id FROM `icmall`.`ecs_goods` WHERE is_delete='0' AND is_real='1' ORDER BY goods_id DESC LIMIT 2111111,20
) as t using (goods_id)