0

I have a query:

 EXPLAIN UPDATE myTable SET col2 = 'foo' WHERE col1 = 'bar';
+----+-------------+-------------+------------+-------+-----------------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys         | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------+------------+-------+-----------------------+---------+---------+------+--------+----------+-------------+
|  1 | UPDATE      | myTable     | NULL       | index | myTable_ind_indx1     | PRIMARY | 4       | NULL | 890860 |   100.00 | Using where |
+----+-------------+-------------+------------+-------+-----------------------+---------+---------+------+--------+----------+-------------+

Which uses the PRIMARY key instead of using the correct index myTable_ind_indx1. Here's the relevant part of the table creation statement:

CREATE TABLE `myTable` (
  `id` int NOT NULL,
  ...
  `col1` varchar(64) DEFAULT NULL,
  ...
  PRIMARY KEY (`id`),
  KEY `myTable_ind_indx1` (`col1`,`col3`),
  ...
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

So it's doing a full table scan instead of using the correct index. At first I thought this is because the query planner estimates the number of rows as ~900k and the total is ~1M, but if I count the number of rows matching the WHERE clause it's actually ~250k and besides even adding a USE INDEX results in the exact same plan. Changing the query to a SELECT results in the correct index being used. This is on MySQL 8.0.19. Any ideas on what I can do to improve things?

9
  • Tried that. Same plan.
    – Johnny
    Oct 18, 2021 at 9:04
  • 1
    Well, if the condition "col1 = 'bar'" matches 250k rows from 1M, that's 25% of your table, that means the update will modify almost all pages of the table, because almost every page will contain at least one row matching the condition (unless there is some direct connection between id and col1 but the optimizer would not know anyway). And if it has to rewrite almost the entire table there is no way skipping randomly using the index would be better than just doing full table scan.
    – jkavalik
    Oct 18, 2021 at 9:17
  • Why is there a difference between SELECT and UPDATE then? I'd expect the same logic to have the SELECT query not use the index either, but that's not the case.
    – Johnny
    Oct 18, 2021 at 9:36
  • what is the SELECT version you use? it might lead to index-only query.
    – jkavalik
    Oct 18, 2021 at 9:46
  • @jkavalik EXPLAIN SELECT * FROM myTable WHERE col1 = 'bar'.
    – Johnny
    Oct 18, 2021 at 9:52

2 Answers 2

1

If the condition col1 = 'bar' matches 250k rows from 1M, that's 25% of your table, that means the update will modify almost all pages of the table, because almost every page will contain at least one row matching the condition (unless there is some direct connection between id and col1 but the optimizer would not know anyway).

And when it has to rewrite almost the entire table there is no way skipping randomly using the index would be better than just doing full table scan. The optimizer is choosing the right plan.

If the rows were actually clustered in one part of the table (there were some correlation between id and col1) then you could tell that to the optimizer by adding the id range to the WHERE clause. But without that a full table scan is just the fastest way to do it.

For the select it may be different, because it does not have to write things to the disk, it may work with different seq/rand access "prices" and utilize caches more. So not having similar plan for SELECT and UPDATE is maybe unexpected but valid.

1
  • @johnny - Did this speed up the Update?
    – Rick James
    Oct 19, 2021 at 18:14
1

Plan A - Chunking

UPDATEing 250K rows takes a long time, regardless of indexing, etc. This is because the Engine hands on to the old values of each of the 250K rows in case you do ROLLBACK or the server crashes. This allows the UPDATE to be atomic.

You want it "faster"? Why? Is it because you want it to finish sooner; if so, why? Or, more likely, the UPDATE is interfering with other stuff.

In this latter case, you can avoid most of the interference by doing the Update in chunks. Walk through the PRIMARY KEY 1K rows at a time, performing an autocommit'd. Details: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

Plan B -- avoid Update

CREATE TABLE newt LIKE mytable;
INSERT INTO newt
    SELECT ...   -- using either data from mytable or other data
        FROM mytable;
RENAME TABLE mytable TO oldt,
             newt TO mytable;
DROP TABLE oldt;

That will have virtually zero impact on other threads reading mytable. And it will probably be faster than the Update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.