Setting the value of one column of all rows is very slow

Question

I have a table containing about 350,000 rows, and I recently switched from MyISAM storage engine to InnoDB.

I am running a query

UPDATE `users` SET `online` = 0 

every time my server starts up and there are no problems using MyISAM. Typically, a query only affects a couple of hundred lines. The query execution time was slow, about 1.5 seconds on average, but I could live with something.

Now that I have changed to InnoDB, the request may take several tens of seconds.

Here is the mysql-slow.log part

 # Query_time: 29.431546 Lock_time: 0.000091 Rows_sent: 0 Rows_examined: 348617 SET timestamp=1372505574; UPDATE users SET online = 0; 

This particular request will change the online value to 200 lines, the others to 0.

I was able to solve the problem by changing the request to

 UPDATE `users` SET `online` = 0 WHERE `online` != 0 

This request took about 0.1 seconds

Now, here is my question. Why does time increase significantly when moving from MyISAM to InnoDB?

And how is it that the request is so slow without the WHERE part? As far as I understand, the MySQL query optimizer is quite powerful, but it says the opposite. What could be causing this very slow query execution time?

The MySQL server version is 5.5.31-0.

Answer 1

In InnoDB, update statements block every row that is checked. This means that to update 200 rows, it must create 350,000 row-level locks while maintaining a rollback lock and providing the previous value for any transactions that read an already modified value (since the transaction is not and the change is not final)

MyISAM, on the other hand, blocks the entire table.

So, if you need to update all rows, lock the entire table and you will get much better performance (you do not need row-level locks)

But even better, specify a WHERE clause, just like you, and InnoDB will only get locks for the corresponding rows (as well as some space locks in the index tree, but this is beyond the scope of the question)

Answer 2

InnoDB implements transaction semantics. That is, he does an extensive job of projecting to other readers of your table the illusion that your online column values ​​are all changing to zero at exactly the same moment. It is also capable if your client or server fails, automatically rolls back the values ​​to the status quo ante state. MyISAM does not care about this. This is a great job for a table with hundreds of thousands of rows.

It does not matter that the values ​​are already zero. He changes them anyway.

When you use the WHERE , you change a lot fewer rows, so there is much less work for its transaction logic.

This transaction logic is a critical function. You used it as a last resort, for which it is not ideal.

Answer 3

you better rewrite the request with

 UPDATE `users` SET `online` = 0 WHERE `online` != 0 

to

 UPDATE `users` SET `online` = 0 WHERE `online` = 1 

indices may look better when using a = vs! =

Check http://sqlfiddle.com/#!2/31088/4 and draw "View the execution plan" there to see what happens here.

 != triggers a range = triggers a ref which should perform better 

As a side note, you should usually not index decks with poor selectivity.