REPLACE query is sometimes very slow

Question

Issue summary

On my site, REPLACE query for qa_options table is very slow. Performance of INSERT and UPDATE are standard. But, only REPLACE is sometimes very slow. In some cases there is a performance difference of 100 times. It is not always occur. For example (worst case) ...

• REPLACE : 300ms - 500ms

My experiment

I was remodeled qa_db_set_option() of qa-include/db/options.php.

1. Case1 (DELETE + INSERT) >>> 7ms - 10ms
2. Case2 (SELECT + INSERT / UPDATE) >>> 0.5ms - 1ms (Best performance!)

If you have information about this issue, please tell me.

http://bugs.mysql.com/bug.php?id=71507 ???

Server spec

• Dedicated server (Web server and database server exist on same hardware)
• MySQL version: 5.0

Comments

Please сan you show us the code that you have changed. I would like to try the example of use on my website.

---

By the time being I've no environment to test this. Anyway, it seems to be an issue strictly related to MySQL development rather than its usage, as it should be even better to use a replace rather than a delete/insert.

What is the effect you notice by using an alternative syntax? EG: INSERT ... ON DUPLICATE KEY UPDATE

Answer 1

Thanks yerbol, pupi.

My hack

qa_db_set_option() of qa-include/db/options.php

    function qa_db_set_option($name, $value)
/*
    Set option $name to $value in the database
*/
    {
        /*
        qa_db_query_sub(
            'REPLACE ^options (title, content) VALUES ($, $)',
            $name, $value
        );
        */
        /*
        // Case1 (DELETE + INSERT)
        qa_db_query_sub('DELETE FROM ^options WHERE title=$', $name);
        qa_db_query_sub('INSERT INTO ^options (title, content) VALUES ($, $)', $name, $value);
        */
        /*
        // Case2 (SELECT + INSERT / UPDATE)
        $assoc = qa_db_read_one_assoc(qa_db_query_sub('SELECT content FROM ^options WHERE title=$', $name), true);
        if (is_array($assoc))
            qa_db_query_sub('UPDATE ^options SET content=$ WHERE title=$', $value, $name);
        else
            qa_db_query_sub('INSERT INTO ^options (title, content) VALUES ($, $)', $name, $value);
        */
        // Case3 (INSERT DUPLICATE KEY UPDATE)
        qa_db_query_sub('INSERT INTO ^options (title, content) VALUES ($, $) ON DUPLICATE KEY UPDATE content=$', $name, $value, $value);
    }

Consideration

Performance of CASE3 (INSERT ... DUPLICATE KEY UPDATE) is good (1ms or less). Since database server is sometimes separated from web server, CASE3 would be good. There are other REPLACE queries into the core. We may need to review all of them. In particular, tables with index (primary key).

Comments

With such small numbers it is hard to trust for a better alternative. In scott's numbers the queries have stressed the DB a little more. I will make some tests on my own too as I wonder if the execution plan for the REPLACE and the ON DUPLICATE KEY UPDATE is the same or not

Answer 2

I made a quick test case, calling each method twice in a loop of 100 times.

REPLACE (current version): 111ms

ON DUPLICATE KEY UPDATE: 25ms

SELECT + INSERT/UPDATE: 107ms

So it seems like the ON DUPLICATE syntax is much faster, even without your intermittent issue. And REPLACE queries are used in several places so the savings would likely be more than negligible.

Comments

Interesting. So maybe it will be needed to change all REPLACE statements. As a side note, the SELECT and INSERT/UPDATE is not semantically the same as the other 2 alternatives because they're are atomic operations while the former isn't

Answer 3

Here is the pull request with all of these changes applied to the core: https://github.com/q2a/question2answer/pull/246

If you guys can test it, that'd be great. Note it is Q2A 1.8 as Scott mentioned he was planning it for that release. However, the changes can safely be merged into 1.7.

Comments

I have tested this and it is working just perfect, no issues at all. Although the installation is fresh so I cannot say about any third party plugin compatibility.