Re: select does too much work to find rows where primary key does not match

[ddevaudreuil]

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 <freedc.bok@stripped> 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 <swagman@stripped>
> 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/mysql?unsub=1
> >
> 
> 
> 
> -- 
> 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.