List:General Discussion« Previous MessageNext Message »
From:RedRed!com IT Department Date:March 15 2006 8:11pm
Subject:Re: What is the proper (least expensive) way to do this
View as plain text  
Martijn Tonies wrote:
> 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
> 
> 


So, for my peace of mind, I would need to follow these procedures to 
ensure Martijn's issue doesn't happen:

1. lock the table
2. execute my select
3. insert if it does not already exist
4. unlock the table

On a high level, is this a correct list of procedures?

Sean
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