find links in one table and not in another (for large tables)

[lli2k5]

Hi,

I'm running:

"SELECT link FROM table1 WHERE link NOT IN (SELECT link FROM table2)"

This command is taking forever where table1 and table 2 are both large tables >100000 rows and where there are over 100,000 different links between them. If I do:

"SELECT link FROM table1 WHERE link NOT IN (SELECT link FROM table2) LIMIT 0,10"

The query works, but any larger selection takes a long time and crashes.

Is there a better way of running such a query? I have primary indexes for this field in both tables.

[NogDog]

This might help, by limiting the number of rows in the sub-query that need to be scanned per main query row:

SELECT link FROM table1 
WHERE NOT EXISTS (SELECT * FROM table2 WHERE table2.link = table1.link) 
LIMIT 0, 10

[lli2k5]

thanks