SELECT COUNT(DISTINCT `tweet`.id_str) FROM `tweet`
`tweet` LEFT JOIN stats ON id_str = stats.tweetId WHERE stats.tweetId IS
NULL

EXPLAINED
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	tweet 	index 	NULL 	PRIMARY 	26 	NULL 	2351 	Using index
1 	SIMPLE 	stats 	ALL 	NULL 	NULL 	NULL 	NULL 	6138 	Using where; Not exists