Mysql:Using filesort处理不掉.唉.
表结构:
CREATE TABLE sarticle
(
id INTEGER(20) NOT NULL AUTO_INCREMENT,
unitid INTEGER(20) default 0 NOT NULL,
siteid INTEGER(20) default 0 NOT NULL,
mtype TINYINT(4) default 0 NOT NULL,
webarticle LONGTEXT NOT NULL,
publishtime DATETIME,
downloadtime DATETIME,
createtime DATETIME,
md5 VARCHAR(16) NOT NULL,
organizationid INTEGER(20) default 0 NOT NULL,
compressed CHAR(1) default '0' NOT NULL,
fingerprint CHAR(32) NOT NULL,
del CHAR(1) default '0',
PRIMARY KEY(id),
UNIQUE (md5, unitid),
INDEX sarticle_pus (publishtime, unitid, siteid),
INDEX sarticle_cus (createtime, unitid, siteid));
查询语句:
SELECT sarticle.id, sarticle.unitid, sarticle.siteid, sarticle.mtype, sarticle.webarticle, sarticle.publishtime, sarticle.downloadtime, sarticle.createtime, sarticle.md5, sarticle.organizationid, sarticle.compressed, sarticle.fingerprint, sarticle.del FROM sarticle WHERE (sarticle.publishtime>='20100709162727' AND sarticle.publishtime<='20100712162727') AND sarticle.unitid IN (3,5,2,4) AND sarticle.siteid IN (28,960,63,41,1105,27,14,987,1256,24,68,37,33,15,11,23,12,7,18,986,38,31,140,32,21,10,35,13,26,988,9,19,8) GROUP BY sarticle.md5 ORDER BY sarticle.publishtime DESC LIMIT 21
explain:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sarticle
type: range
possible_keys: sarticle_pus
key: sarticle_pus
key_len: 13
ref: NULL
rows: 66
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)
当数据量增大时,mysql总出现coping to tmp......
请大侠排忧解难!!查阅好久也没解决..