Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.

REPLACE query is sometimes very slow

+5 votes
1,111 views
asked Apr 28, 2015 in Q2A Core by sama55
edited Apr 28, 2015 by sama55

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
Q2A version: 1.7
commented Apr 28, 2015 by yerbol89kz
Please сan you show us the code that you have changed. I would like to try the example of use on my website.
commented Apr 28, 2015 by pupi1985
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

3 Answers

+3 votes
answered Apr 28, 2015 by sama55
edited Apr 28, 2015 by sama55

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).

commented May 4, 2015 by pupi1985
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
+3 votes
answered May 4, 2015 by Scott
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.
commented May 4, 2015 by pupi1985
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
+4 votes
answered May 30, 2015 by pupi1985
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.
commented Aug 5, 2015 by Gurjyot Singh
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.
...