From: Patrick J. McEvoy
Date: April 15 2008 9:00pm
Subject: select does too much work to find rows where primary key does not match
List-Archive: http://lists.mysql.com/mysql/212299
Message-Id: <215E50D8-78A1-4FE0-A19E-5B266F49869C@swagman.com>
MIME-Version: 1.0 (Apple Message framework v919.2)
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

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.