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]
