![]() |
Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL) - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: General (https://forum.codeigniter.com/forum-1.html) +--- Forum: Lounge (https://forum.codeigniter.com/forum-3.html) +--- Thread: Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL) (/thread-66089.html) |
Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL) - skunkbad - 09-04-2016 I see that there may be some performance issues when using "ON DUPLICATE KEY UPDATE". Do you use it? Check this MY_Model and let me know what you think: PHP Code: <?php RE: Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL) - InsiteFX - 09-05-2016 I would think that any foreign key check would be a performance hit because it would need to check every record. RE: Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL) - skunkbad - 09-05-2016 If you take a look at actual benchmark data, like this for example: https://www.facebook.com/notes/mysql-at-facebook/the-overhead-of-on-duplicate-key-update/268006035932/ Then the performance issue seems pretty benign. Consider the link that shows an image of the results in a graph. I'm not saying there is no performance issue, but given the limited use case where it is not know if a record exists, then it's probably nothing to worry about. I found myself needing this recently. RE: Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL) - InsiteFX - 09-06-2016 Yes in your case if it out weighs the performance hit then yes I would use it. RE: Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL) - dave friend - 09-07-2016 I think the performance hit on this sort of statement comes when you are trying to update the key of an existing record. Otherwise, it performs on par with an insert or update. I confess I did not look that closely at your code but isn't what you're doing basically served by $this->db->replace() in Query Builder? RE: Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL) - skunkbad - 09-07-2016 (09-07-2016, 03:06 PM)dave friend Wrote: I think the performance hit on this sort of statement comes when you are trying to update the key of an existing record. Otherwise, it performs on par with an insert or update. REPLACE is actually different, in that it deletes the row and then creates an entirely new row. I suppose it accomplishes the same thing, but I haven't tested it out. Since the website I'm working on is running on CI 2.x with plans to upgrade but at the end of a very long list of things to do, I'd have to create a replace function too. |