Another reader pointed out that I actually want to group the ORs together, which allows the query to use the index on foo_id. I've also been experimenting with multiple SELECTs and UNIONs like so:
SELECT bar.foo_id, foo_equivalency.foo_id FROM bar JOIN foo_equivalency ON id = bar.a_id WHERE foo_equivalency.foo_id IN (367,365,327,269,197,387,379,361,331) AND type = 'a_id' UNION SELECT bar.foo_id, foo_equivalency.foo_id FROM bar JOIN foo_equivalency ON id = bar.b_id WHERE foo_equivalency.foo_id IN (367,365,327,269,197,387,379,361,331) AND type = 'b_id' UNION SELECT bar.foo_id, foo_equivalency.foo_id FROM bar JOIN foo_equivalency ON id = bar.foo_id WHERE foo_equivalency.foo_id IN (367,365,327,269,197,387,379,361,331) AND type = 'foo_id' The EXPLAIN looks slightly better (160+14+1), I think: +-----------------+--------+--------------------------+--------------+------ ---+--------------------+------+--------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------------+--------+--------------------------+--------------+------ ---+--------------------+------+--------------------------+ | foo_equivalency | range | foo_id,type,foo_and_type | foo_and_type | 3 | NULL | 20 | Using where | | bar | ref | a_id | a_id | 3 | foo_equivalency.id | 8 | Using where | | foo_equivalency | range | foo_id,type,foo_and_type | foo_and_type | 3 | NULL | 7 | Using where | | bar | ref | b_id | b_id | 3 | foo_equivalency.id | 2 | Using where | | foo_equivalency | ref | foo_id,type,foo_and_type | type | 1 | const | 1 | Using where | | bar | eq_ref | PRIMARY | PRIMARY | 2 | foo_equivalency.id | 1 | Using where; Using index | +-----------------+--------+--------------------------+--------------+------ ---+--------------------+------+--------------------------+ But, of course, those UNIONs still mean a full join. Is this as good as I'm going to get? Thanks for the previous replies! ____________________________________________________________ Eamon Daly NextWave Media Group LLC Tel: 1 773 975-1115 Fax: 1 773 913-0970 ----- Original Message ----- From: "Dathan Vance Pattishall" <[EMAIL PROTECTED]> Sent: Monday, May 17, 2004 12:09 PM Subject: RE: Indexes ignored when using SELECT foo FROM a, b? > 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
