How does MySQL index not speed up update query?

Question

I have a table located in RAM and doing some performance tests.

Let's consider a sample query, adding explain sentences along with results

mysql> explain update users_ram set balance = balance + speed where sub = 1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | UPDATE      | users_ram | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2333333 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)

mysql>  update users_ram set balance = balance + speed where sub = 1;
Query OK, 1166970 rows affected (0.37 sec)
Rows matched: 1166970  Changed: 1166970  Warnings: 0

As you can see, it takes 0.37 sec without index. Then I'm creating an index on the sub column, which is an int column with just two possible values of 0 and 1, and surprisingly nothing changes

mysql> create index sub on users_ram (sub);
Query OK, 2333333 rows affected (2.04 sec)
Records: 2333333  Duplicates: 0  Warnings: 0

mysql> show index from lords.users_ram;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users_ram |          0 | user     |            1 | user        | NULL      |     2333333 |     NULL | NULL   | YES  | HASH       |         |               |
| users_ram |          1 | sub      |            1 | sub         | NULL      |           2 |     NULL | NULL   |      | HASH       |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> explain update users_ram set balance = balance + speed where sub = 1;
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
|  1 | UPDATE      | users_ram | NULL       | range | sub           | sub  | 5       | const | 1166666 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
1 row in set (0.00 sec)

mysql>  update users_ram set balance = balance + speed where sub = 1;
Query OK, 1166970 rows affected (0.37 sec)
Rows matched: 1166970  Changed: 1166970  Warnings: 0

If I remove the index and add it again, but now using btree, it gets even more weird

mysql> explain update users_ram set balance = balance + speed where sub = 1;
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
|  1 | UPDATE      | users_ram | NULL       | range | sub           | sub  | 5       | const | 1057987 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
1 row in set (0.00 sec)

mysql> update users_ram set balance = balance + speed where sub = 1;
Query OK, 1166970 rows affected (0.62 sec)
Rows matched: 1166970  Changed: 1166970  Warnings: 0

How could adding an index could have no effect or even slow down the query?

Let's take into account that I'm not modifying the column which is indexed, so mysql doesn't have to do an extra write operation, so really I can't get what's really happening here.

Because you are updating a massive number of rows. Indexes are blazing fast when you access a very small percentage of the rows -- not your case. — The Impaler
– The Impaler, Commented Mar 31, 2021 at 22:27
An index on a column whose value is 0 or 1 isn't very useful. All it does is divide the table in two. You still have to update half the rows (on average). — user207421
– user207421, Commented Mar 31, 2021 at 23:24

Rick James · Accepted Answer · 2021-04-01 18:05:12Z

1

"table located in RAM" -- I suspect that is technically incorrect. The possibilities (in MySQL):

The table lives on disk, but it is usually fully cached in the in-RAM "buffer_pool".
The table is ENGINE=MEMORY. But that is used only for temp stuff; it is completely lost if the server goes down.

update users_ram set balance = balance + speed where sub = 1;

The table users_ram needs some index starting with sub. With such, it can go directly to the row(s). But...

It seems that there are 1166970 such rows. That seems like half the table?? At which point, the index is pretty useless. But...

Updating 1M rows is terribly slow, regardless of indexing.

Plan A: Avoid the UPDATE. Perhaps this can be done by storing speed in some other table and doing the + whenever you read the data. (It is generally bad schema design to need huge updates like that.)

Plan B: Update in chunks: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

How the heck did you get index-type to be HASH? Perhaps `ENGINE=MEMORY? What version of MySQL?

What is speed? Another column? A constant?

Please provide SHOW CREATE TABLE users_ram -- There are some other things we need to see, such as the PRIMARY KEY and ENGINE.

(I need some of the above info before tackling "How could adding an index could have no effect or even slow down the query?")

answered Apr 1, 2021 at 18:05

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nicael Over a year ago

Thank you! Well, there's a comment which told me that anyway I update more-or-less half the table and therefore the index doesn't help. My table is engine=memory indeed, and that's right, index-type is hash automatically with this type of table. As I mentioned, I've also tried btree, which appeared to slow down the query. In the end, I decided to split my updates in 20 parts, by adding an additional indexed column which is randomly populated with numbers from 0 to 19, and went with 20 separate updates, so I have no more issue.

nicael Over a year ago

I couldn't update in different times, e.g. once a user opens the app, because the concept is - every balance is updated every minute, growing by the speed, which is variable and depends on "upgrades" a user buys. So, currently I made it this way: during a minute, evenly there're 20 evenly distributed parts updating, and each update takes so small time that I don't care anymore

nicael Over a year ago

As for your concern about the fact that table is erased each reboot - well, I'll set up a back-up to persist on disk every N minutes. I couldn't go with any other table than engine=memory because of these big updates, again. They're taking much less time in RAM, than they would if would be written on disk every update.

Rick James Over a year ago

@nicael - I don't know if MEMORY benefits from chunking; did the "20" run faster or better in some other way? Sounds like you are happy with what you have.

Collectives™ on Stack Overflow

How does MySQL index not speed up update query?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related