MySQL Forums
Forum List  »  Performance

Is index needed or not?
Posted by: Bogdan Ursu
Date: January 18, 2010 08:20AM

Hello guys,

I have the following table:
CREATE TABLE `items_types` (
`item_id` int(11) unsigned NOT NULL,
`type` enum('t1','t2','t3','t4','t5') NOT NULL default 't1',
PRIMARY KEY (`item_id`,`type`),
KEY `item_id` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I am doing a LEFT JOIN `items_types` `i_t` ON `i_t`.`item_id` = `i`.`item_id` a lot so I need this to use an index.

Do I need the extra KEY on `item_id`? Or I can just remove it and be confident that MySQL would use the PRIMARY KEY correctly for that JOIN?

The MySQL version is 5.0.87-community on a CentOS 5.4 server.

I'm pretty sure of the answer, I will also use an EXPLAIN to test both cases. But until then, I would like an expert opinion.

Thanks

Free Flash Components at http://flashxml.net

Options: ReplyQuote


Subject
Views
Written By
Posted
Is index needed or not?
3196
January 18, 2010 08:20AM
1383
January 19, 2010 09:18PM
1475
January 20, 2010 12:37AM


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.