Mysql find unmatched records from large two tables

First, add indexes on files and downloads. This will make searches much faster. This might take a few minutes.

ALTER TABLE files ADD INDEX (filename); ALTER TABLE downloads ADD INDEX (filename); 

Then, use a LEFT JOIN instead of a subquery.

SELECT f.* FROM files f LEFT JOIN downloads d ON d.filename = f.filename WHERE d.filename IS NULL AND f.filename <> '' 

After these changes, the search should take less than a second.

A better way to write the query would be this (assuming you have an id column):

SELECT a.* FROM files a LEFT JOIN downloads b ON b.filename = a.filename WHERE b.id IS NULL AND a.filename != '' 

The error is likely coming back due to a timeout in the PHP script. If this query still doesn't run quickly enough, please post the output of the above query with EXPLAIN at the beginning so we can see what MySQL is doing.

It would be better to left_join 2 tables, so, we take all records from files table and add to them appropriate records from downloads. Filenames from files which have null as a pair from downloads are the ones we need, so, we filter to get only such ones.

select f.filename from files as f left join downloads as d on f.filename=d.filename where d.filename is null and f.filename<>'' 

My implementation is just 2 simple scans:

mysql> explain select f.filename from files as f left join downloads as d on f.filename=d.filename where d.filename is null and f.filename<>''; SIMPLE f Using where; Using index; SIMPLE d Using where; Using index; Not exists 

Original one uses subquery:

mysql> explain SELECT * FROM files WHERE filename <> '' AND filename NOT IN (SELECT filename FROM downloads); PRIMARY files DEPENDENT SUBQUERY downloads