I can't get a join to process as it takes too long. I don't know how long it will take as I stop it after a few minutes...
Two tables in separate databases
Table fruit (4,000,000 records)
Table user (175,567 records)
What I want to do is extract all names from user from a specific location.
e.g. In the fruit table find all records for 'north' and then display the users.user corresponding info (should be 9975 records)
Here is the explain
EXPLAIN SELECT a.* FROM user AS a JOIN food.fruit AS b ON b.date=a.date AND b.fruit=a.fruit WHERE b.location='north'
1,SIMPLE,a,ALL,date,fruit,'','','',175567 Using where;Using join buffer
The first bit looks correct in that it has found the required 9975 rows but the second is not. There are indexes on date and fruit and yet it wants to pull all 175567 records from user rather than the 9975 corresponding records.