List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:March 15 2006 7:58pm
Subject:Re: What is the proper (least expensive) way to do this
View as plain text  
Connie,


> I have a database for ASN information in which I save asn information for
reference by other scripts (asn lookup can be expensive).
>
> CREATE TABLE `ASNINFO` (
> `asnInfoID` int(11) NOT NULL auto_increment,
> `asNumber` int(11) NOT NULL default '0',
> `description` varchar(255) default NULL,
> `comment` varchar(255) default NULL,
> `f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
> PRIMARY KEY (`asnInfoID`),
> UNIQUE KEY `asNumber` (`asNumber`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> Multiple Ips can share the same ASN value.
>
> Is it better to do an 'insert ignore' into ASNINFO when I have to look one
up, or is it better to try to fetch the ASN information to see if it exists,
and then if it does not exist, do the insert. I figure the fetch is one sql
command, and the insert is one sql command, so if an asn does not exist, it
is two data base accesses, where if I do an 'insert ignore' it is only one
command and if the value is in the table, the insert is ignored.
>

The "insert ignore" sounds a bit hackish to me.

Given that "asNumber" is indexed via the unique key, it will be fast anyway.

That being said, it is entirely possible that someone will insert the
row you are looking for between your first select and the insert
in case you didn't find it. So you would have to handle that case
anyway.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Thread
What is the proper (least expensive) way to do thisConnie A. Logg15 Mar
  • Re: What is the proper (least expensive) way to do thisMartijn Tonies15 Mar
    • Re: What is the proper (least expensive) way to do thisRedRed!com IT Department15 Mar
      • Re: What is the proper (least expensive) way to do thisgerald_clark16 Mar
  • MySQL Query Browser 1.1.20 problemMojtaba Faridzad15 Mar