Is there a PRIMARY or UNIQUE key on the table already?

If not,

ALTER IGNORE TABLE `tablename` ADD PRIMARY KEY (`term`,`url` ) ;

This will add the key and also remove any duplicates.

INSERT IGNORE tablename (term,url) VALUES ('$term','$url') ON DUPLICATE KEY UPDATE countercolumn = countercolumn + 1

This will increment counter column by one if the term/url combo already exists. It relies on the primary/unique index and reverts to updating when the value already exists.

[dev.mysql.com...]

In general, you should try to avoid using an ON DUPLICATE KEY clause on tables with multiple unique indexes.

That looks like it should do the trick but there is a current primary key, it's an auto-increment ID field. Should I remove it? If the count increases on individual entries when a duplicate attempt is made then I don't need the auto-increment ID field anymore. Or do I if it's best not to use ON DUPLICATE.. ?

Thanks for the response.

Hard to say. Removing it puts you at risk of having duplicate values for the current PK as auto_increments have to be an index of some sort (in this case, a standard INDEX that accepts duplicates).

If you don't foresee that as a problem and you don't regularly query the current PK row then feel free to give it a try. If you don't reference the current PK field in INSERT's to the table then the duplicate problem should never arise.

If the table's primary role is to store unique $term/$url combinations and the current PK is merely a more compact reference to it, I'd say change to the new PK.

I changed to the new PK you suggested and it works, I didn't reference the ID anyway. One thing of note is that there is a 1000 byte limit on PKs so the combination of keywords and url's can't exceed 1000 characters. So - with varchar 255 on keyword and varchar 744 on url I shouldn't run into any problem, I don't think. I don't need long keyword strings and want to leave room for long urls in case of parameters etc...

Thanks your your help!

Indeed, I've never considered 1000 byte keys in practice, even if it's multi-byte text encoding...

You could probably get away with a 16 byte PRIMARY key by adding a BINARY(16) column for MD5 values.

INSERT IGNORE tablename (hash,term,url) VALUES (UNHEX(MD5('$term$url')),'$term','$url') ON DUPLICATE KEY UPDATE countercolumn = countercolumn + 1

// added

If you were to use MD5, you'd probably want to turn the $term URL to lowercase if case is unimportant for that field, as it would generate separate MD5's for lower and upper case.

[edited by: brotherhood_of_LAN at 10:43 pm (utc) on Aug 3, 2011]

I don't think I'll need the hash for this particular use but thats a great idea.

One last question re: timestamp

I'd like to add a timestamp column that uses ON UPDATE CURRENT_TIMESTAMP so that the data can be sorted by most recent, do you envision any conflict with the above? I don't need the date each search was performed and it would be complicated to store anyway with the above.

[edited by: Sgt_Kickaxe at 10:46 pm (utc) on Aug 3, 2011]

That would be fine. ON DUPLICATE KEY essentially is an INSERT or UPDATE in the one query. It's just like a regular insert/update.

It's working almost flawlessly.

The apparent remaining bug is that the url table is ignoring not null on urls occasionally. It seems to be getting ignored 1 time in 25 from Google only. I do have some php that first tries to extract the google web url variable (url) and if that fails it tries the image url variable (imgrefurl) but I don't see any spacing issues. Something has to be in there or the php would not open the database connection, a string perhaps?

Any ideas what might cause the empty value in a not null field?

If you think it's the value pairs that are going into the DB, try logging it in a tab delim list and have mysql_affected_rows() alongside the insert data

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.

So you'll see how it's behaving from the start.

Though I'm not quite sure by what you mean by

is ignoring not null on urls occasionally

If URL/term are a primary key, all unique combinations are inserted and the increment column is updated otherwise. If data is not being inserted/updated at all it's happening before this query or the query is returning an error (have you escaped the data?)

I tracked the problem down to my handling of images in search that appear as thumbnails in regular web results. The database is fine.

Thanks again.