Looking up duplicate record or adding new unique record

[Phil Bitis]

CREATE TABLE tbllayer (
LayerID int(11) NOT NULL default '0',
LayerSize int(11) NOT NULL default '0',
IceTypeID int(11) NOT NULL default '0',
Fingerprint char(16) binary default NULL,
PRIMARY KEY (LayerID),
UNIQUE KEY Fingerprint (Fingerprint),
KEY IceTypeID (IceTypeID)
) TYPE=MyISAM;

We have an internet monitoring application which stores objects in the above
table, with the fingerprint an MD4 of the object. In general about 30% of
the time an object monitored is already in the table, in which case we don't
want to re-insert it, we just want to find out it's ID. The percentage may
vary between 10% and 50% though.

Currently we have a cache in our application which works like this:

- object is monitored and its fingerprint taken
- is the fingerprint in the cache? if so, take its ID from there
- if not, do a SELECT on the table - if a match is found add it to the cache
and use its ID
- if not, INSERT the record into the tablem use its ID and possibly add it
to the cache too

Ok. In general, is it better to:

- do a SELECT to see if the record exists and if not INSERT it
or
- do an INSERT, and if it fails then do a SELECT to locate the record

What about if the duplicate ratio is high or low?

Cheers,
-Phil

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw