Search code examples
mysqlquery-optimizationmyisam

Which Query is good in query optimization as per high trafic server with MyISAM storage engine?

Following are two queries. which one is good as per query optimization point of view.

//Query 1
UPDATE `table_name` SET `column` = '455' WHERE `row_id` = '433200';

//Query 2
UPDATE `table_name` SET `column` = `column`+1 WHERE `row_id` = '433200';

Thanks, Yogs

Solution

  • I think that the difference would be negligible if you don't force MySQL to convert your strings to integers. If the types are integers, don't wrap the values in quotes:

    UPDATE `table_name` SET `column` = 455 WHERE `row_id` = 433200;

    And

    UPDATE `table_name` SET `column` = `column` + 1 WHERE `row_id` = 433200;

    However, the queries say two different things. The first one, where you explicitly set the value, is not thread safe. If you have two or more sessions, both trying to increment the value, whichever one ran last would set the value, and most likely the other values would be lost. For example, if three connections ran the query at the same time, the value might only be incremented once. To avoid this, you'd have to explicitly lock the table before the SELECT and release it after the UPDATE.

    The second query, where you increment the value, is thread safe. If three connections all ran the query at the same time, the value would be incremented three times.