The mysql_affected_rows function

[Barry]

Tweet

Hi all

just a little tech question that I am up struggling over.

I have a database and I am trying to check for a duplicate value in a row before I insert a new row (Its not a unique column). I dont want to go down the select and check route as it takes alot of server load so I am trying to use the mysql_affected_rows function to check for duplicates.

The trouble is that the value is being returned as 0 even if the query matches - I think this is because its not actually updating the row with any new information just literally checking and 'updating' it with the same info.

Does anyone have any ideas? Or am I just overtired and getting confused!!?

ta

Baz

using php5
mysql 4.1

[Spartacus]

Baz,

There might be a better solution than this, and I'm not overly clear on what you are trting to do, but I would introduce a new column of updated_date of type DATETIME and set it to now()

Then something will change and affected rows will return 1

[Barry]

Hi Ya

thanks yeah I tried that and it works if I cron the script at time intervals however on the first pass to import all the data it happens so quick there are duplicates as I import as is happens within a micro second - I am using the mysql date() command - is there a date command that is perhaps stored in milliseconds?

Ta

Baz

[trevHCS]

With MySQL assuming it's the MyISAM table type, you might find doing the SELECT and then doing the INSERT if required is actually quicker. MySQL does SELECT statements very fast but UPDATE / INSERT requires a tablewide lock which takes a while to apply, do the operation and release.

With regards the 0 return value - from what I've seen in the past it seems MySQL is doing it's own SELECT and then UPDATE sequence whenever you do an UPDATE, thus if the data is the same as existing it skips it and doesn't need to lock / unlock the tables. Not sure if this applies to non-indexed columns, but certainly seen it even when not going via PHP.


The other other solutions I can find to this require a unique key, but they're not exactly for this. This is a bit of a wild idea, but I was wondering if you could create a key using several fields which together will always be unique.

Then use something like the INSERT ON DUPLICATE KEY UPDATE and set it to do nothing useful in the UPDATE bit (eg: SET id=id). If the value exists then it'll go do the UPDATE bit and if not then it'll insert the new row.


Final note - remember if you do an INSERT or UPDATE on the table and also use indexes to speed up SELECT's, each UPDATE/INSERT requires MySQL to recreate the appropriate index keys thus adding more work to the system which can be significant if there's a lot of data in those indexes.


Update - missed the milliseconds bit:

It doesn't seem MySQL even v5 does this very well. Plus milliseconds aren't going to be unique enough for a busy server, but unfortunately microseconds don't seem to be an option at all in MySQL. Only thing I could think there would be adding the value of usleep() from PHP into the database as an INT, then everytime you run an INSERT statement you send it the usleep value and maybe use the INSERT ON DUPLICATE thing above to skip updating if it exists. Just remember to index the value of this new column for this to work.

Trev - probably making zero sense...

[Barry]

I did actually write two in scripts in the end before reading this, the first script for importing using SELECT to check for duplicates and another for updating using the mysql_affected_rows using a date column to 'force' an update.

However looking at your post it looks like it would be better for me to use the SELECT function to check for duplicates. Thanks for the info, I'll give it a whirl.

Its not like its mission critical stuff I am doing but its all interesting stuff to know and does make sense.

Ta

Baz