Daevid Vincent wrote:
> Well, my "hack" (which is sort of like what you suggest) is to change my
> primary key from just an auto_increment 'id' field to a combination of two
> other fields (mac/scanner_id) that I know must be unique. Then I rely upon
> the fact that mySQL will not allow a duplicate PK. (I did say it was a
> hack). A co-worker assures me that a SELECT is cheap, however a version I
> tried (without my hack) still allowed duplicates to slip through because I
> wasn't locking the tables. I have multiple scanners hitting the same table
> and locking seems to me a bad idea.
> Also, I guess my TIMESTAMP brainstorm won't work b/c the resolution of that
> field is 1 second and these queries happen faster than that. *Neuman!* :-/
> REPLACE INTO won't work, as I need the previous record (hence the update). I
> store the first and last time I saw a node, amongst other info. REPLACE
> would delete that data.
I believe what Steve suggests is the cleanest solution under the assumption you
are willing to move to 4.1. If not, your college has a very good point - any
one-row operation on MySQL is very fast - on modern hardware (AMD XP 2200+ or
faster) you are looking at the order of magnitude of 10,000 per seconds.
Create online surveys at http://www.surveyz.com/