How about using a left outer join. Find all the rows in bar without a matching row in foo:
To verify: select * from bar left outer join foo on bar.phone=foo.phone where foo.phone is null Then delete bar.* from bar left outer join foo on bar.phone=foo.phone where foo.phone is null Phil <[EMAIL PROTECTED]> wrote on 04/15/2008 05:32:38 PM: > 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 ! > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > CONFIDENTIALITY NOTICE:This email is intended solely for the person > or entity to which it is addressed and may contain confidential > and/or protected health information. Any duplication, > dissemination, action taken in reliance upon, or other use of this > information by persons or entities other than the intended recipient > is prohibited and may violate applicable laws. If this email has > been received in error, please notify the sender and delete the > information from your system. The views expressed in this email are > those of the sender and may not necessarily represent the views of > IntelliCare.
