how to insert a new link into a table with a million entries?

lli2k5

I'm writing a program that tries to add a link into a database that already has a million links. The thing is, I want to check first if the link exists and then add it if it doesn't it.

I do
if(@mysql_num_rows(mysql_query("SELECT link FROM table WHERE link='$link'")){}
else{ mysql_query("INSERT INTO table(link) VALUES('$link')")}

The thing is, the program is taking longer and longer to run.

Is this the best way of checking whether a link already exists in a database?

greensweater

Make sure you have an index on the link field, it will speed up the query.

If you make the link field the primary key, you could use "replace" instead of "insert"... or check for an error since you can't have a duplicate key.

lli2k5

The link field is declared as a text field. Would that affect runtime performance compared to a varchar?

Also, if you insert into a table, do you have to rebuild the index again before being able to use it on the new data?

Thanks for your help.

rulian

To answer your question, you wont have to re-index when you insert a row, the index will maintain that itself.

I could be wrong, but since you do not compare your if statement to a condition, your if will return true whether it finds a row or not.

To up your performance you could do a couple of things. Having million of rows is what mysql was designed for, so if it's lagging there is something wrong.

I dont konw enought about your database or program to figure out what, but something I could suggest is set link to a primary unique key if it has to be unique in the table, That way you dont have to reduntantly chekc and then insert a link, just tyr to insert the link and it will return error on match, saving you 1 query, but in your 2 query process that makes your program x2 as fast already.

lli2k5

Thanks man

TF_TheMoon

You're querying on a text field, I hope the table is innodb and that that field is properly indexed. If its a MyISAM you're going to lock the table when you hit the insert

Scooby_Doo

Ok, I can't tell if you've already found your answer but this should work

$LINK = $_POST['LINK'];

                                                        $sql = "SELECT * FROM `TABLE` WHERE `ROW` ='$ROW'";
                                                        $res = mysql_query($sql) or die(mysql_error());
                                                        if(mysql_num_rows($res) > 0){
                                                        echo "This link already exsist";
                                                        }else {

                                                                    $sql = "INSERT INTO `TABLE` (`LINK`)VALUES ('$LINK');";
                                                                    $res = mysql_query($sql) or die(mysql_error());
                                                                    echo "Your Link has been added successfully";


?>

You'll have to tweak it a LOT but it should work. I hope this helps

bradgrafelman

The above code will indeed work, but it's definitely not optimized at all, nor should it ever be used in a production environment (mysql_error() should never be shown).

What exactly is in this "link" field? A URL? If so, I would recommend you change that column to VARCHAR and add a UNIQUE index on it.

That way, you don't have to worry about doing a SELECT query first - just run the INSERT query and check for the duplicate key error # (1061 I believe?).

EDIT: Also note that it's really hard to do this type of checking; what if I type in phpbuilder.com, someone else does www.phpbuilder.com, someone else does phpbiulder.com/, etc. etc.?

Scooby_Doo

bradgrafelman wrote:
EDIT: Also note that it's really hard to do this type of checking; what if I type in phpbuilder.com, someone else does www.phpbuilder.com, someone else does phpbiulder.com/, etc. etc.?

To answer this part (I did this once when I was first learning)

I made a form like this.

<form action="addlink2.php" method="POST">
<table border="3">
<tr>
<td>http://www.<input type="text" name="url" value="DON'T ADD .COM, ect"></td>  

<td><select name="extension">
<option>.com</option>
<option>.net</option>
<option>.org</option>
<option>ect</option>
</select>
</td>
</tr>
<tr>
<td>Links name (Example <a href="#">This is the name</a>)
<input type="text" name="urlname"></td>
</tr>
<tr>
<td>Submit:</td>   <td><input type="submit" value="Add Link"></td>
</tr>
</table>
</form>

The point of having the extensions is so they don't type it in wrong.

You would display the link like this.

<?php
  $url = $_POST['url'];
  $extension = $_POST['extension'];
  $urlname = $_POST['urlname'];
?>
<html>
...
<body>
<a href="http://www.<?=$url; $extension;?>"><?=$urlname; ?></a>
</body>
</html>

Hope this helps!

bradgrafelman

There are hundreds of TLD's; are you going to type out all of them and keep the list updated as they add new ones?
What if they didn't want the www subdomain?

This is why it's hard to filter a type of thing like this.

Scooby_Doo

I understand what your saying, and I didn't mean that it was impossible to add a link twice. I was just giving an idea on how to make it easer for the person to enter the same link twice. That was just a simple form that was used to keep people from typing in www. or .com