RolandoMySQLDBAon Database Administrators Stack Exchange • Recommends
On “MySQL subquery slows down drastically, but they work fine independently”
In Query 3, you are basically executing a subquery for every row of mybigtable against itself.
To avoid this, you need to make two major changes:
MAJOR CHANGE #1 : Refactor the Query
Here is your original query
Select count(*) as total from mybigtable
where account_id=123 and email IN
(select distinct email from mybigtable where account_id=345)
You could try
select count(*) EmailCount from
(
select tbl123.email from
(select email from mybigtable where account_id=123) tbl123
INNER JOIN
(select distinct email from mybigtable where account_id=345) tbl345
using (email)
) A;
or maybe the count per email
select email,count(*) EmailCount from
(
select tbl123.email from
(sel…