Re: What is the proper (least expensive) way to do this
RedRed!com IT Department wrote: 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 I would just do the insert, and check for a duplicate key error. If I get the duplicate key error, then update the record if necessary. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is the proper (least expensive) way to do this
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. Thanks, Connie Connie Logg, Network Analyst Stanford Linear Accelerator Center ph: 650-926-2879 Happiness is found along the way, not at the end of the road, and 'IF' is the middle word in life. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the proper (least expensive) way to do this
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the proper (least expensive) way to do this
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
