SELECT *
FROM myitem C
WHERE 1=1 and endTime <= '2011-03-24 00:00:00' and endTime >= '2011-03-22 00:00:00' and listingType = '123'我已经建立了 endTime和listingType 的联合索引explain输出的type字段为All, possible_keys字段可以出现 e_l索引,但是key字段为空但是如果我把endTime时间缩小到一天的时候
endTime <= '2011-03-24 00:00:00' and endTime >= '2011-03-23 00:00:00' type字段为range,为possible_keys字段和key字段都为e_l索引,这是为什么呢?是不是跟范围有关的
FROM myitem C
WHERE 1=1 and endTime <= '2011-03-24 00:00:00' and endTime >= '2011-03-22 00:00:00' and listingType = '123'我已经建立了 endTime和listingType 的联合索引explain输出的type字段为All, possible_keys字段可以出现 e_l索引,但是key字段为空但是如果我把endTime时间缩小到一天的时候
endTime <= '2011-03-24 00:00:00' and endTime >= '2011-03-23 00:00:00' type字段为range,为possible_keys字段和key字段都为e_l索引,这是为什么呢?是不是跟范围有关的
myitem 0 PRIMARY 1 myItemID A 279560 BTREE
myitem 1 itemID 1 itemID A 279560 YES BTREE
myitem 1 sellerID 1 sellerID A 5 YES BTREE
myitem 1 e_l_u 1 endTime A 279560 YES BTREE
myitem 1 e_l_u 2 listingType A 279560 YES BTREE
1 SIMPLE C ALL e_l_u 276736 Using where
1 SIMPLE C range e_l_u e_l_u 9 30744 Using where
SQL code你的表中一共才279560条记录,结果符合 endTime <= '2011-03-24 00:00:00' and endTime >= '2011-03-22 00:00:00' 就有276736条。建议你检查一下SELECT count(*)
FROM myitem C
WHERE 1=1 and endTime <= '2011-03-24 00:00:00' and endTime >= '2011-03-22 00:00:00'
2万多条啊,疯了,到底是什么原因,要搜20多万条啊
ebay_api.myitem analyze status OK怎么这么乱,有时endTime >= '2011-03-19 00:00:00' and endTime <= '2011-03-22 00:00:00' 可以
凡是相差一天可以, 两天以上就不行
FROM myitem C
WHERE 1=1 and endTime <= '2011-03-24 00:00:00' and endTime >= '2011-03-22 00:00:00' and listingType = '123';select count(*) from myitem ;分别看看,加上WHREE能排除掉多少记录。
唯一性太差的索引一般是用不上的。