刚接触mysql不久,对索引理解不深,请教一下
表item
字段 fdate,OutletID,fcheck,fitem,ftotal,fcode ....
索引:fdate,OutletID,fcheck,fcode,fitem
索引名称为:OutletID
SQL语句:explain SELECT fdate,OutletID,fcheck,ftotal FROM item
WHERE fdate>= '20090101' AND fdate<= '20100601' AND OutletID IN (1,2,3) AND void=0;
输出结果
id select_type table type possible_key key key_len ref rows extra
1 SIMPLE sales_itemtrs ALL OutletID \N \N \N 838022 Using where
很明显上面没有用到索引,但是我把SQL语句里面的ftotal去掉
explain SELECT fdate,OutletID,fcheck FROM itemtrs
WHERE fdate>= '20090101' AND fdate<= '20100601' AND OutletID IN (1,2,3) AND void=0;
输出结果
id select_type table type possible_key key key_len ref rows extra
1 SIMPLE sales_itemtrs index OutletID,FDATE OutletID 90 \N 838022 Using where; Using indx这样就用到了索引,索引到底是怎样运作的呢?上面2个SQL语句有什么区别吗?
表item
字段 fdate,OutletID,fcheck,fitem,ftotal,fcode ....
索引:fdate,OutletID,fcheck,fcode,fitem
索引名称为:OutletID
SQL语句:explain SELECT fdate,OutletID,fcheck,ftotal FROM item
WHERE fdate>= '20090101' AND fdate<= '20100601' AND OutletID IN (1,2,3) AND void=0;
输出结果
id select_type table type possible_key key key_len ref rows extra
1 SIMPLE sales_itemtrs ALL OutletID \N \N \N 838022 Using where
很明显上面没有用到索引,但是我把SQL语句里面的ftotal去掉
explain SELECT fdate,OutletID,fcheck FROM itemtrs
WHERE fdate>= '20090101' AND fdate<= '20100601' AND OutletID IN (1,2,3) AND void=0;
输出结果
id select_type table type possible_key key key_len ref rows extra
1 SIMPLE sales_itemtrs index OutletID,FDATE OutletID 90 \N 838022 Using where; Using indx这样就用到了索引,索引到底是怎样运作的呢?上面2个SQL语句有什么区别吗?
贴出来看一下。
show index from item;这样别人也好给你做测试例子。