CODEWITHSUNDEEP
mysqlsqlmariadb
Rorschach
Rorschach

Reputation: 3802

How to make an efficient UPDATE like my SELECT in MariaDB

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 tried 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?

OR

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

Notes

Version: 10.3.23-MariaDB-log

Upvotes: 0

Views: 259

Answers (2)

Nick
Nick

Reputation: 147166

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)

Upvotes: 0

Barmar
Barmar

Reputation: 780909

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

Upvotes: 2

Related Questions