Can anybody explain the difference on index and primary or unique key? I have a test table which looks like this
CREATE TABLE temp (
id tinyint(1) unsigned NOT NULL AUTO_INCREMENT,
item varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
type
varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
value
tinyint(2) unsigned DEFAULT NULL,
PRIMARY KEY (id),
KEY key_type (type
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
explain select * from temp where id =1;
±—±------------±------±------±--------------±------- -±--------±------±-----±------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±------- -±--------±------±-----±------+
| 1 | SIMPLE | temp | const | PRIMARY | PRIMARY | 1 | const | 1 | |
±—±------------±------±------±--------------±------- -±--------±------±-----±------+
explain select * from temp where type = ‘tool’;
±—±------------±------±-----±--------------±-------- -±--------±------±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±--------------±-------- -±--------±------±-----±------------+
| 1 | SIMPLE | temp | ref | key_type | key_type | 18 | const | 1 | Using where |
±—±------------±------±-----±--------------±-------- -±--------±------±-----±------------+
If primary is considered as an index, then why is it that when I use ‘id’ in where I get blank ‘extra’ column when using explain? Unlike when I use ‘tool’ in where, I get the using where value in ‘extra’ column. To confuse me more, when I do
explain select id from temp where type = ‘tool’;
±—±------------±------±-----±--------------±-------- -±--------±------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±--------------±-------- -±--------±------±-----±-------------------------+
| 1 | SIMPLE | temp | ref | key_type | key_type | 18 | const | 1 | Using where; Using index |
±—±------------±------±-----±--------------±-------- -±--------±------±-----±-------------------------+
I get the expected using index value in the extra column.
I modified the table so that the ‘type’ field in the table is unique. Then same thing happened. The ‘extra’ column is blank. I just noticed that the ‘type’ column in explain now shows const instead of ref. Does this mean that having a type const in the ‘type’ column in explain eliminates the detection of using where?
Thanks!