Use force index to force the index lookup on foo_id If that doesn't work try analyze table on that table and run the explain again.
The OR will not allow you to use a compound index but the primary key or 1st key-foo_id should be used. I just noticed that your table definition foo_id is not defined as a primary key, so running analyze table will get things into perspective. DVP ---- Dathan Vance Pattishall http://www.friendster.com > -----Original Message----- > From: Eamon Daly [mailto:[EMAIL PROTECTED] > Sent: Monday, May 17, 2004 7:32 AM > To: [EMAIL PROTECTED] > Subject: Indexes ignored when using SELECT foo FROM a, b? > > I have a table structured like so: > > CREATE TABLE `foo_equivalency` ( > `foo_id` smallint(6) NOT NULL default '0', > `type` enum('a_id','b_id','foo_id') NOT NULL default 'foo_id', > `id` smallint(6) NOT NULL default '0', > KEY `foo_id` (`foo_id`), > KEY `type` (`type`) > ) TYPE=MyISAM > > I'm using a SELECT to pull all of the equivalent foo_id's > from another table, bar, like so: > > SELECT bar.foo_id, foo_equivalency.foo_id FROM bar, foo_equivalency > WHERE > foo_equivalency.foo_id IN (367,365,327,269,197,387,379,361,331) AND > (type = 'a_id' and id = bar.a_id) OR > (type = 'b_id' and id = bar.b_id) OR > (type = 'foo_id' and id = bar.foo_id) > > foo_id is a primary key in table bar, and I created test > indexes on a_id and b_id: > > PRIMARY KEY (`foo_id`), > KEY `a_id` (`a_id`), > KEY `b_id` (`b_id`) > > EXPLAIN reveals that no indexes are being used: > > +-----------------+------+-------------------+------+---------+------+---- > -- > +-------------+ > | table | type | possible_keys | key | key_len | ref | > rows > | Extra | > +-----------------+------+-------------------+------+---------+------+---- > -- > +-------------+ > | bar | ALL | PRIMARY,a_id,b_id | NULL | NULL | NULL | > 269 > | | > | foo_equivalency | ALL | foo_id,type | NULL | NULL | NULL | > 2931 > | Using where | > +-----------------+------+-------------------+------+---------+------+---- > -- > +-------------+ > > I'm unclear as to why none of the indexes apply, not even > the PRIMARY on foo_id. Is it the ORs that blow everything > away? Can I work around it? > > It occurs to me that I could split foo_equivalency into 3 > tables (foo_equivalency_by_a, foo_equivalency_by_b, and > foo_equivalency_by_foo), rather than using the enum and a > conditional, but that seems really unwieldy and slow (3 > selects and then a UNION). Suggestions? I'm on MySQL 4.0.18, > by the way, so subselects aren't an option. > > ____________________________________________________________ > Eamon Daly > NextWave Media Group LLC > Tel: 1 773 975-1115 > Fax: 1 773 913-0970 > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
