How to make an efficient UPDATE like my SELECT in mariadb-CodePudding

Background

I made a small table of 10 rows from a previous SELECT already ran (SavedAnimals).

I have a massive table (animals) which I would like to UPDATE using the rows with the same id as each row in my new table.

What I have tired so far

I can quickly SELECT the desired rows from the big table like this:

mysql> EXPLAIN SELECT * FROM animals WHERE ignored=0 and id IN (SELECT animal_id FROM SavedAnimals);

 ------ -------------- ------------------------------- -------- --------------- --------- --------- ---------------------------------------------------------- ------ ------------- 
| id   | select_type  | table                         | type   | possible_keys | key     | key_len | ref                                                      | rows | Extra       |
 ------ -------------- ------------------------------- -------- --------------- --------- --------- ---------------------------------------------------------- ------ ------------- 
|    1 | PRIMARY      | <subquery2>                   | ALL    | distinct_key  | NULL    | NULL    | NULL                                                     |   10 |             |
|    1 | PRIMARY      | animals                       | eq_ref | PRIMARY       | PRIMARY | 8       | db_staging.SavedAnimals.animal_id |    1 | Using where |
|    2 | MATERIALIZED | SavedAnimals | ALL    | NULL          | NULL    | NULL    | NULL                                                     |   10 |             |
 ------ -------------- ------------------------------- -------- --------------- --------- --------- ---------------------------------------------------------- ------ -------------

But the "same" command on the UPDATE is not quick:

mysql> EXPLAIN UPDATE animals SET ignored=1, ignored_when=CURRENT_TIMESTAMP WHERE ignored=0 and id IN (SELECT animal_id FROM SavedAnimals);
 ------ -------------------- ------------------------------- ------- --------------- --------- --------- ------ ---------- ------------- 
| id   | select_type        | table                         | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
 ------ -------------------- ------------------------------- ------- --------------- --------- --------- ------ ---------- ------------- 
|    1 | PRIMARY            | animals                       | index | NULL          | PRIMARY | 8       | NULL | 34269464 | Using where |
|    2 | DEPENDENT SUBQUERY | SavedAnimals | ALL   | NULL          | NULL    | NULL    | NULL |       10 | Using where |
 ------ -------------------- ------------------------------- ------- --------------- --------- --------- ------ ---------- ------------- 
2 rows in set (0.00 sec)

The UPDATE command never finishes if I run it.

QUESTION

How do I make mariaDB run with the Materialized select_type on the UPDATE like it does on the SELECT?

Is there a totally separate way that I should approach this which would be quick?

Notes

Version: 10.3.23-MariaDB-log

CodePudding user response：

Use JOIN rather than WHERE...IN. MySQL tends to optimize them better.

UPDATE animals AS a
JOIN SavedAnimals AS sa ON a.id = sa.animal_id
SET a.ignored=1, a.ignored_when=CURRENT_TIMESTAMP
WHERE a.ignored = 0

CodePudding user response：

You should find an EXISTS clause more efficient than an IN clause. For example:

UPDATE animals a
SET a.ignored = 1, 
    a.ignored_when = CURRENT_TIMESTAMP
WHERE a.ignored = 0
  AND EXISTS (SELECT * FROM SavedAnimals sa WHERE sa.animal_id = a.id)