I have a MySQL database in which table A has a one-to-many relation to table B, and I would like to select all rows in table B that have no children in table A. I have tried using
SELECT id FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE B.id=A.id)
and
SELECT id FROM A LEFT JOIN B ON A.id=B.id WHERE B.id IS NULL
Both of these seem slow. Is there a faster query to achieve the same thing?
In case this is relevant, in my database table A has about 500,000 rows and table B has about 3 to 4 million rows.
Edit: For the actual tables in my database, explain gives me:
+----+--------------------+------------------+-------+---------------+---------------------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------+-------+---------------+---------------------------+---------+------+---------+--------------------------+
| 1 | PRIMARY | frontend_form471 | index | NULL | frontend_form471_61a633e8 | 32 | NULL | 671927 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | SchoolData | index | PRIMARY | PRIMARY | 49 | NULL | 3121110 | Using where; Using index |
+----+--------------------+------------------+-------+---------------+---------------------------+---------+------+---------+--------------------------+
for
select number from frontend_form471 where not exists (select * from SchoolData where SchoolData.`f471 Application Number`=frontend_form471.number)
and
+----+-------------+------------------+-------+---------------+---------------------------+---------+------+---------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+---------------+---------------------------+---------+------+---------+------------------------------------------------+
| 1 | SIMPLE | frontend_form471 | index | NULL | frontend_form471_61a633e8 | 32 | NULL | 671927 | Using index; Using temporary |
| 1 | SIMPLE | SchoolData | index | PRIMARY | PRIMARY | 49 | NULL | 3121110 | Using where; Using index; Not exists; Distinct |
+----+-------------+------------------+-------+---------------+---------------------------+---------+------+---------+------------------------------------------------+
for
select distinct number from frontend_form471 left join SchoolData on frontend_form471.number=SchoolData.`f471 Application Number` where SchoolData.`f471 Application Number` is NULL
where in my case frontend_form471 is table A and SchoolData is table B
Edit2: In table B (SchoolData) in my database, id is the first part of a two part primary key, so it is indexed and there are still multiple entries in B with the same id.