mySQL efficiency with inner join queries 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(*) &gt; 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(*) &gt; 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):
Based (only) on your query i think the better query could be :

 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(*) &gt; 1;


the correct index could be in   table a  only this: 

KEY cust_and_rec (customer_id, record_id)


in table b only 

KEY cust (custmer_id)


In the index you show in your question seem rc, test and test2 are the same and not  so selective like the inverse (customer_id is the most selective value in your query )

index rc on table b seem not useful for your purpose.