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.
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]
Re: Indexes ignored when using SELECT foo FROM a, b?
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]
