question on index and primary key

[edwonglsc]

I have the following table - stat
+----------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | PRI | | |
| page | varchar(255) | NO | PRI | | |
| time | timestamp | NO | | 0000-00-00 00:00:00 | |
+----------+--------------+------+-----+---------------------+----------------+

all 3 primary keys are shown as index when I do the (show index query)

However, when I run the following explain statement:

mysql> explain select * from stat where username = "milly";
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | stat | ALL | NULL | NULL | NULL | NULL | 13196 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

The username should be indexed, so how come it doesn't show type, key and row correctly?

any reason? i am confused.

thx

 

[r937]

please do a SHOW CREATE TABLE

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[edwonglsc]

| stat | CREATE TABLE `stat` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(20) NOT NULL,
`page` varchar(255) NOT NULL,
`time` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`,`username`,`page`)
) ENGINE=MyISAM AUTO_INCREMENT=13359 DEFAULT CHARSET=latin1 |

wondering whether the username is actually indexed or not...

 

[r937]

username isn't indexed, no

may i ask why you have a 3-part primary key?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[edwonglsc]

omg... i thought primary key will be indexed automatically... thx

 

[r937]

a primary key always ~is~ indexed

but why do you have a 3-column primary key? the auto_increment is sufficient by itself to be the primary key

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[edwonglsc]

yup. my mistake.
will use single primary key (id)
and index(username)