2 indices work differently in the same table?

[HN Hsieh]

We have a table whose CREATE TABLE statement is:


CREATE TABLE `tbl_account` (
`accountid` int(11) NOT NULL auto_increment,
`id1` int(11) NOT NULL default '0',
`id2` int(11) NOT NULL default '0',
`money` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`createtime` int(11) NOT NULL default '0',
`terminatetime` int(11) NOT NULL default '0',
PRIMARY KEY (`accountid`),
KEY `idx_freeid1` (`id1`),
KEY `idx_freeid2` (`id2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


And the INSERT statement of test data is:


INSERT INTO `tbl_account` VALUES (1,100,200,0,1,0,0),(2,300,100,0,2,0,0),(3,300,200,0,2,0,0),(4,300,400,0,1,0,0),(5,400,100,0,2,0,0),(6,200,400,0,2,0,0),(7,700,800,0,1,0,0),(8,600,900,0,1,0,0),(9,600,700,0,2,0,0),(10,800,800,0,2,0,0),(11,200,100,0,2,0,0);

Both id1 and id2 are indexed, I thought they might work the same.
When I tried to explain the following SELECT statements, I got different results, but I don't know why. Can somebody tell me why they are different?
By the way, the MySQL version is "5.0.15-standard" and OS is Linux.

mysql> explain select * from tbl_account where id1 in (100,800)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_account
type: range
possible_keys: idx_freeid1
key: idx_freeid1
key_len: 4
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from tbl_account where id2 in (100,800)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_account
type: ALL
possible_keys: idx_freeid2
key: NULL
key_len: NULL
ref: NULL
rows: 11
Extra: Using where
1 row in set (0.00 sec)


Thanks for your help.



Edited 1 time(s). Last edit at 01/03/2006 01:35AM by HN Hsieh.