mysql - trying to figure out why index isn't being used -

so have 2 tables 1 row in common - `type`. row key in both tables , type same in both tables - varchar(4). problem is, when join tables using column explain makes tho column isn't being used key.

my schema + data:

create table demo (   id int auto_increment,   `type` varchar(4),   data varchar(255),   primary key (id),   key `type` (`type`) );  create table types (   id int auto_increment,   `type` varchar(4),   `desc` varchar(255),   primary key (id),   key `type` (`type`) );  insert demo (type, data) values ('aaaa','aaaa'),('aaaa','aaaa'),('bbbb', 'bbbb'); insert types (type, `desc`) values ('aaa','aaaa'),('bbbb','bbbb'); 

my query:

explain select * demo d join types t on d.`type` = t.`type`; 

per explain query isn't searching through 3x rows (3*1) - it's having search through 6x rows (3*2). seems me ought searching through 3x rows?

sql fiddle:

http://sqlfiddle.com/#!9/d6b69d/2

any ideas?

thanks!

i barely renamed few columns , table name(s) unrelated answer below. got results initially. following solved it

analyze table demo; 

-- status ok

analyze table thetypes; 

-- status ok

-- table/index statistics updated

-- note readlock @ table level in manual pages analyze table

explain select * demo d force index (thetype) join thetypes t force index (thetype) on t.thetype=d.thetype 

-- u results

-- note verbeage index hints use/force in manual paged

so whether using 6 rows or 6 million take peek

to index names if not obvious run

show index demo;

yes named columns thetype , not type