accounts, products, users
Then filter them out from products table where user bought more than 3 products (Need to count users in product table and exclude a user if his count is more than 3).
SELECT id, email FROM users WHERE id IN ( SELECT user_id FROM accounts WHERE users.id = user_id )
I got all users id and emails, but now I need to filter them out from product table. In product table I have 'product_id' and 'leader_id', which is user id.
I need optimized query, because tables are very big
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `accounts` ADD INDEX `accounts_idx_user_id` (`user_id`);
SELECT users.id, users.email FROM users WHERE EXISTS ( SELECT 1 FROM accounts WHERE ( users.id = accounts.user_id ) AND ( users.id = accounts.user_id ) )