MySQL Forums
Forum List  »  Optimizer & Parser

Table JOIN causes FULLSCAN despite of indeces
Posted by: Denis
Date: April 19, 2007 11:58AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Table JOIN causes FULLSCAN despite of indeces
2806
April 19, 2007 11:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.