SELECT b.* FROM b WHERE b.DEAL_FLAG=1 ORDER BY b.SEND_ALL_ID desc limit 0,10;
SELECT b.* FROM b WHERE b.DEAL_FLAG!=1 ORDER BY b.SEND_ALL_ID desc limit 0,10;
b表数据2百万,SEND_ALL_ID是主键, deal_flag字段绝大多数值是1,很小一部分是其他值(如0,-1,-2),问题在于如何加索引使上面两个sql查询速度都很快?
SELECT b.* FROM b WHERE b.DEAL_FLAG!=1 ORDER BY b.SEND_ALL_ID desc limit 0,10;
b表数据2百万,SEND_ALL_ID是主键, deal_flag字段绝大多数值是1,很小一部分是其他值(如0,-1,-2),问题在于如何加索引使上面两个sql查询速度都很快?
第二个查询要用新加的索引会比较快
且这样做SELECT count(*) FROM b WHERE b.DEAL_FLAG=1 需要3秒以上
deal_flag重复率比较高标示列不用创建索引的。
select b.* from b ,( select SEND_ALL_ID from FROM b WHERE b.DEAL_FLAG=1 ORDER BY b.SEND_ALL_ID desc limit 0,10) a
where b.SEND_ALL_ID=a.SEND_ALL_ID;