Optimize: Finding the records in one table that are not in another table

Databases
1

How can I optimize one of these queries:


SELECT 
  COUNT(*) 
FROM 
  a 
WHERE 
  id NOT IN (
    SELECT aid FROM b
  )

Desc:
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	a 	index 	NULL 	PRIMARY 	4 	NULL 	836 	Using where; Using index
2 	DEPENDENT SUBQUERY 	b 	ALL 	NULL 	NULL 	NULL 	NULL 	836 	Using where




SELECT 
  COUNT(*) 
FROM 
 a 
LEFT JOIN 
   b 
  ON 
   a.id = b.aid 
  WHERE 
    b.aid IS NULL

Desc:
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	a 	index 	NULL 	PRIMARY 	4 	NULL 	836 	Using index
1 	SIMPLE 	b 	ALL 	NULL 	NULL 	NULL 	NULL 	836 	Using where; Not exists

They take about 1 sec to run on 1k records in each table…
I have indexes on id and aid.

I know there is a way to make this faster, but i can’t figure it out, can someone help?

2

are you sure you have an index on aid? the EXPLAINs indicate it isn’t there

3

darn it, one of the slaves didn’t have it, and somehow it always hit that one.

Thanks for the heads up.

Is it possible to optimize it a bit more?


SELECT 
  COUNT(*) 
FROM 
  a 
WHERE 
  id NOT IN (
    SELECT aid FROM b
  )

Desc:
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY a 	index 	NULL 	PRIMARY 	4 	NULL 	836 	Using where; Using index
2 	DEPENDENT SUBQUERY 	b 	index_subquery aid 	aid 	4 	func 	1 	Using index; Using where