I would have thought your not = though is matching a lot more rows every time..
I would look into using where not exists as a subselect delete from bar where not exists (select 'y' from foo where foo.phone = bar.phone); something like that. On Tue, Apr 15, 2008 at 5:00 PM, Patrick J. McEvoy <[EMAIL PROTECTED]> wrote: > I have two MyISAM tables; each uses 'phone' as a primary key. Finding rows > where the primary keys match is efficient: > > mysql> explain select bar.phone from foo,bar where foo.phone=bar.phone; > > +----+-------------+-------+--------+---------------+---------+---------+---------------+-------+-------------+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > > +----+-------------+-------+--------+---------------+---------+---------+---------------+-------+-------------+ > | 1 | SIMPLE | bar | index | PRIMARY | PRIMARY | 10 | > NULL | 77446 | Using index | > | 1 | SIMPLE | foo | eq_ref | PRIMARY | PRIMARY | 10 | > ssa.bar.phone | 1 | Using index | > > +----+-------------+-------+--------+---------------+---------+---------+---------------+-------+-------------+ > 2 rows in set (0.00 sec) > > > Finding rows in one table that do not match a row in the other table is > wildly inefficient: > > mysql> explain select bar.phone from foo,bar where foo.phone!=bar.phone; > > +----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > > +----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+ > | 1 | SIMPLE | bar | index | NULL | PRIMARY | 10 | > NULL | 77446 | Using index | > | 1 | SIMPLE | foo | index | NULL | PRIMARY | 10 | > NULL | 3855468 | Using where; Using index | > > +----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+ > 2 rows in set (0.00 sec) > > (This is the same for 'NOT', '!=', or '<>'.) > > The amount of work should be identical in both cases: grab a row, look up > by primary key in the other table, proceed. > > My real goal is to delete rows in the smaller table if there is no match > in the larger table: > > delete from bar using foo,bar where not bar.phone=foo.phone; > > but it runs for hours. I suppose I could SELECT INTO a new table and > rename the tables, but that seems dorky. > > Is there any way to force SELECT/DELETE to look up the primary key rather > than scan the entire index? > > Thanks. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- Help build our city at http://free-dc.myminicity.com !
