I've a problem
There're couple of tables:
CREATE TABLE `items` (
`id` int(11) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM;
and
CREATE TABLE `rates` (
`id` int(11) unsigned NOT NULL,
`rate` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM;
Execution of this query:
SELECT `rate` FROM `rates` LEFT JOIN `items` USING (`Id`)
Sometimes takes more than 2 seconds on a rather good server on both tables having about 30'000 rows
EXPLAIN shows following:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE rates ALL 33168 ""
1 SIMPLE items eq_ref PRIMARY,id PRIMARY 4 mydreams.rates.id 1 Using index
i.e. we have a full rows scan,
Than I create a composite index with `rates`.`id` и `rates`.`rate` columns result is not much better:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE rates index rateid 8 33168 Using index
1 SIMPLE items eq_ref PRIMARY,id PRIMARY 4 mydreams.rates.id 1 Using index
DB: MySQL 5.0.22 (win32) and MySQL 5.0.26 (OpenSuSE)
Help me please.