CodeIgniter Forums

Full Version: Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL)
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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
defined
('BASEPATH') OR exit('No direct script access allowed');

class 
MY_model extends CI_Model {

    /**
     * Class constructor
     */
    public function __construct()
    {
        parent::__construct();
    }

    // -----------------------------------------------------------------------

    /**
     * Here, we're just dynamically creating a query that 
     * is in the following format:
     *
     *   INSERT INTO `table` (`field1`, `field2`, ...)
     *               VALUES ('value1', 'value2', ...) 
     *   ON DUPLICATE KEY UPDATE 
     *               `field1` = 'value1',
     *               `field2` = 'value2',
     *               ...
     * 
     * @param  string $table The table name
     * @param  array $data  The data to be inserted or updated
     * @return void
     */
    public function insert_or_update_if_exists$table, array $data 
    {
        // Basic parameter validation
        if( empty( $table ) )
            throw new Exception('Supplied table name cannot be empty.');
        if( empty( $data ) )
            throw new Exception('Data array cannot be empty.');

        // In case there is an existing record, prepare data for update
        $update_values = [];
        $update_arr = [];
        foreach$data as $k => $v )
        {
            $update_values[] = $v;
            $update_arr[] = sprintf"`%s` = ?"$k );
        }

        // Create insert/update SQL
        $sql sprintf
            "%s ON DUPLICATE KEY UPDATE %s"
            $this->db->insert_string$table$data ), 
            implode','$update_arr )
        );

        // Do the insert or update
        $this->db->query$sql$update_values );
    }

    // -----------------------------------------------------------------------
}

/* End of file MY_Model.php */
/* Location: /application/core/MY_Model.php */ 
I would think that any foreign key check would be a performance hit because it would need to check every record.
If you take a look at actual benchmark data, like this for example:

https://www.facebook.com/notes/mysql-at-...006035932/

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.
Yes in your case if it out weighs the performance hit then yes I would use it.
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?
(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.

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?

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.