With REPLACE, you just set up the query the same as an INSERT statement but otherwise it just works. With ON DUPLICATE UPDATE you have to set up the whole query with the entire text all over again as an update. The query strings for what I'm doing are in some cases pushing enough text in medical report fields that it uses a MediumText data type, and I am watchful of bandwidth and performance, so this seems better - I'm not sending the field names and values twice. It is also something I don't have to program, I can just set the engine. The performance bottleneck is NOT likely going to be MySQL with either engine, but the processes creating these queries have some limitations.
Anyway, I just thought I would share. BTW I experimented, and innoDB does updates and fires off update triggers for REPLACE statements, but MyISAM does delete/inserts. Jim McNeely On Dec 19, 2011, at 1:28 PM, Claudio Nanni wrote: > Good to know and good that you took time to read the manual, good approach. > > But why bother with REPLACE if you will go with INSERT.....ON DUPLICATE KEY > UPDATE? > > The storage engine is a property of your table and you can set it and/or > change it, it is the low-level layer (physical) of the database that takes > care on how data is actually stored and retrieved. > > You can check your table with: > > SHOW TABLE STATUS LIKE 'your-table-name'; > > Manual page: http://kae.li/iiiga > > Cheers > > Claudio > > 2011/12/19 Jim McNeely <j...@newcenturydata.com> > >> In the MySQL documentation, we find this tantalizing statement: >> >> "It is possible that in the case of a duplicate-key error, a storage >> engine may perform the REPLACE as an update rather than a delete plus >> insert, but the semantics are the same. There are no user-visible effects >> other than a possible difference in how the storage engine increments >> Handler_xxx status variables." >> >> Does anyone know what engine this is? I can't seem to find any info via >> google. If I could live with the choice of engine, I could make this work >> with no extra programming at all. >> >> Thanks, >> >> Jim McNeely >> >> On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: >> >>> Only if you can change the application you could use INSERT....ON >> DUPLICATE >>> KEY UPDATE instead of REPLACE. >>> >>> Check Peter's post here: http://kae.li/iiigi >>> >>> Cheers >>> >>> Claudio >>> >>> >>> 2011/12/17 Jim McNeely <j...@newcenturydata.com> >>> >>>> Here is a fun one! >>>> >>>> I have a set of tables that get populated and changed a lot from lots of >>>> REPLACE statements. Now, I need an ON UPDATE trigger, but of course the >>>> trigger never gets triggered because REPLACES are all deletes and >> inserts. >>>> >>>> The trigger is going to populate another table as a queue for a system >> to >>>> do something whenever a particular field changes. >>>> >>>> SO, does anyone have some slick idea how to handle this little dilemma? >> I >>>> have an idea but I have a feeling there is something better out there. >>>> >>>> Thanks! >>>> >>>> Jim McNeely >>>> -- >>>> MySQL General Mailing List >>>> For list archives: http://lists.mysql.com/mysql >>>> To unsubscribe: http://lists.mysql.com/mysql >>>> >>>> >>> >>> >>> -- >>> Claudio >> >> > > > -- > Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
