468,769 Members | 2,272 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,769 developers. It's quick & easy.

Looking up duplicate record or adding new unique record

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

Jul 19 '05 #1
0 1387

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Catherine Jo Morgan | last post: by
6 posts views Thread by planetthoughtful | last post: by
4 posts views Thread by Thomas Arthur Seidel | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.