Welcome Guest, Not a member yet? Register   Sign In
Do you ever use "ON DUPLICATE KEY UPDATE"? (MySQL)
#1

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 */ 
Reply
#2

I would think that any foreign key check would be a performance hit because it would need to check every record.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

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.
Reply
#4

Yes in your case if it out weighs the performance hit then yes I would use it.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#5

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?
Reply
#6

(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.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB