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.
