using index puzzle

[abednegoyulo]

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!

[gmouse]

The behaviour you describe is due to NULL.

const vs. ref is because of uniqueness of a PK or UNIQUE index: at most a single row will be returned (though unique allows different rows where value is NULL; if you use WHERE type IS NULL and use a UNIQUE index, I expect you to get ref instead of const).

[abednegoyulo]

Thanks gmouse! But there was data on the table. My bad for forgetting to mention this. I entered two records for testing purposes which populated all four columns. So definitely I don’t have nulls in the table. Any ideas?

Thanks for your patience!

[sterin71]

(A little late post but I started out trying to answer your original questions some day ago)

abednegoyulo wrote on Mon, 01 August 2011 02:07

Can anybody explain the difference on index and primary or unique key?

An index is basically an ordered list (but I guess you already knew that part ).
A unique index is a index that can only contain unique values, so there are no duplicates in that list.
The primary key is basically a unique index which identifies each record in the table and is used as the primary way to find a record, hence the name primary key. So it’s basically a unique index by another name.

But on InnoDB the primary key is also special.
InnoDB physical storage is in a b-tree based on the primary key, where the table data is stored on each leaf. The advantage with this approach is that if you select a record by a primary key value all data will be readily available when you find that entry in the index.

abednegoyulo wrote on Mon, 01 August 2011 02:07

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?

Using index does not mean what I think you want it to mean.
Using index means that all information to resolve the query was available in the index itself. Not if it was using an index or not to find the record. The column “key” tells you which index that was used.

The Extra field should basically be treated as an extra field and not something that you should rely on. To tell you the truth I remember I thought it was misguiding at times when I started out learning to understand the explain output.