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

[Phil <freedc.bok@xxxxxxxxx>]

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@xxxxxxxxxxx>
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=pchapman@xxxxxxxxx
>



-- 
Help build our city at http://free-dc.myminicity.com !