add a correct index, the same select statement becomes 5 times slower

[qiu cheng]

help!!!

I met a very strange problem:adding a correct index, the same select statement becomes 5 times slower.

my envirnment is:
windows 2003 enterprise server
mysql 5.1.34 community server

in a small table called down_messages which has about 1k rows :

CREATE TABLE `down_messages` (
`from` varchar(100) NOT NULL DEFAULT '',
`from_id` varchar(32) DEFAULT NULL,
`msg_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`publish_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`msg_type` enum('1','2','3','4','5') CHARACTER SET latin1 NOT NULL DEFAULT '1',
`content` varchar(20000) DEFAULT NULL,
`summary` varchar(500) DEFAULT NULL,
`url` varchar(300) DEFAULT NULL,
`title` varchar(300) DEFAULT NULL,
`begin_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`to_id` varchar(20) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

excute the following statement:
SELECT * FROM down_messages WHERE msg_id>=1 AND msg_type='4' AND
to_id IN ('o1803','all','f2222231','f82006573','o1803|1234','l1','l1|o1803','l1|o1803','l1|o1803','l1|o1803','l1|o1803','l1|o1803','l1|o1803','l1|o1803','l1|o1803','l1|o1803','l1|o1803','l1|o1803')
AND begin_time<=NOW() AND end_time>=NOW()
GROUP BY msg_id
ORDER BY msg_id

profiling info is:
Status Duration
starting 0.000008
checking query cache for query 0.000051
Opening tables 0.000009
System lock 0.000002
Table lock 0.000009
init 0.000042
optimizing 0.000009
statistics 0.000006
preparing 0.000009
Creating tmp table 0.000047
executing 0.000001
Copying to tmp table 0.001064
Sorting result 0.000012
Sending data 0.000021
end 0.000001
removing tmp table 0.000003
end 0.000001
query end 0.000001
freeing items 0.000028
logging slow query 0.000001
cleaning up 0.000002

explain the select statement gets the following results:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE down_messages ALL \N \N \N \N 1267 Using where; Using temporary; Using filesort

Adding an index like this:
UNIQUE KEY `NewIndex1` (`msg_type`,`msg_id`,`to_id`,`begin_time`,`end_time`)

explain the select statement gets:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE down_messages ref NewIndex1 NewIndex1 1 const 12 Using where

so explain results prove the index is used, let's see the profile result:
Status Duration
starting 0.000007
checking query cache for query 0.000044
Opening tables 0.000004
System lock 0.000002
Table lock 0.000003
init 0.000048
optimizing 0.000007
statistics 0.000052
preparing 0.000010
executing 0.000006
Sorting result 0.000001
Sending data 0.005166
end 0.000001
query end 0.000001
freeing items 0.000027
logging slow query 0.000001
cleaning up 0.000002

With a correct index, the same select statement get 5 times slower, why?