I have a script that adds about 100,000 entries to SQL if it doesn't exist. But it normally takes about 30 hours to fully check each row and add if it doesnt exist. Is there an easier way to do this?
my code currently uses a for Loop, within the loop is this.
$query = mysql_query("SELECT EXISTS (SELECT * FROM linkdb WHERE link='$currentlink')");
if (mysql_result($query, 0) == 1){
}else{
$qry = "INSERT INTO linkdb(link,title) VALUES('$link','$title')";
$result = @mysql_query($qry);
}
the code above takes very long time because it has to normally go through thousands of entries. If I don't check the table first using SELECT EXIST and use only INSERT INTO, 90,000 entries are added within 1 min. But that adds duplicate entries of the same row.
Please give me some advice on what I could do. These rows need to be updated almost everyday.
You're looking for ON DUPLICATE KEY UPDATE. Add an index on link and then:
INSERT INTO linkdb(link,title) VALUES('$link','$title') ON DUPLICATE KEY UPDATE link=link;
With that said, you should not be using ext/mysql since it is deprecated. Instead look into PDO or mysqli. It would be much better to use parametrized queries for this to prevent SQL injection.
Perhaps
INSERT INTO ... ON DUPLICATE KEY UPDATE
can solve your problem.
If you don't want to update the value when there is a duplicate, you can combine the two queries into one:
INSERT INTO linkdb(link,title)
select '$link','$title'
where not exists (SELECT * FROM linkdb WHERE link='$currentlink'))
In practice, you can speed up any of these queries by creating an index on linkdb(link).