I have two tables, both with about 1,000,000 rows.
Here is the question I am trying to get mySQL to solve:
- return all record_ids from table_a where that id matches the cat_id in table_b, but only for customer_id 654 (customer_id exists in both tables).
First off I tried this with the following statement:
SELECT record_id from `table_a` where customer_id="654" and record_id in (SELECT cat_id from `table_b` where cat_id="654");
This is the most logical looking one to me, as it's more at my coding level. But this query took 2 minutes to run. So I tried to re-write it using:
SELECT record_id from `table_a` where customer_id = "654" and exists (SELECT cat_id FROM `table_b` where table_b.customer_id = "654" and table_a.record_id= table_b.cat_id group by record_id having count(*) > 1);
This took 22 seconds to run.
I tried another way as well:
SELECT table_a.record_id from table_a INNER JOIN table_b ON (table_a.record_id = table_b.cat_id) WHERE table_a.customer_id = "654" and table_b.customer_id= "654" GROUP BY table_a.record_id HAVING COUNT(*) > 1;
This one takes 19.5 seconds to run.
I'm wondering if my third attempt is the most efficient way to ask this question, or as I'm hoping, there is a more efficient way to run these.
Here is mySQL's execution path for the last two queries:
On table_a, I have the following indices:
KEY `rc` (`record_id`,`customer_id`) KEY `test` (`record_id`,`customer_id`), KEY `test2` (`record_id`,`customer_id`), KEY `cust` (`customer_id`)
On table_b, I have the following indices:
KEY `rc` (`cat_id, `customer_id`)
There are duplicates that I made on accident and I haven't gotten around to deleting them, (not sure if that impacts performance):