[Resolved] Insert/Select infinite loop

[cbrknight]

I am pulling data from file into a mysql database table... then I am comparing one field from that table with the main database table... if the record exists in the main table I am adding it to table bad.. if the record doesn't exist I am adding it to table good.

When I run my sql statement I am going into an infinite loop... any ideas why?

$sql4 = ("INSERT IGNORE INTO good(telephone)(Select upload_file.telephone FROM upload_file,mainfile WHERE upload_file.telephone!=mainfile.telephone");

$r4 = mysql_query($sql4,$deduper)or die("ERROR: ".mysql_error());

$sql5 = ("INSERT IGNORE INTO good(telephone)(Select upload_file.telephone FROM upload_file,mainfile WHERE upload_file.telephone=mainfile.telephone");

$r5 = mysql_query($sql5,$deduper)or die("ERROR: ".mysql_error());

[tomhath]

It's probably not infinite, but I don't doubt it takes a long time.

Let's assume (as an example) that you have 1000 rows in upload_file, and 5000 rows in mainfile.

Let's also assume that each row in the upload_file matches exactly one row in mainfile.

So that means each row in upload_file does NOT match 4999 rows in mainfile. Which means this query will insert 4999 rows (from mainfile) for every one of the 1000 rows in upload_file. A total of 4,999,000 rows will be inserted (given the example numbers I used).

INSERT IGNORE INTO good(telephone)
(Select upload_file.telephone 
FROM upload_file,mainfile 
WHERE upload_file.telephone!=mainfile.telephone

To insert the one row you will need something like this:

INSERT IGNORE INTO good(telephone)
(Select upload_file.telephone 
FROM upload_file
LEFT JOIN mainfile 
ON upload_file.telephone=mainfile.telephone
WHERE mainfile.telephone is null