0

I have a table that tracks the action of my users (used for logging and debugging for their purpose).

From time to time, I need to mark the data as treated by doing the following call :

UPDATE actions
SET is_treated = 1
WHERE code = 200
    AND is_treated = 0
    AND account_uuid = :uuid
    AND DATE(executed) = UTC_DATE()
LIMIT 1000

I do that command inside a loop that loop as long as there are "is_treated" at 0 for that day. I limited to 1000 to fasten the query.

That table currently have around 1M entries.

Despite setting the limit at 1000, the query fails from time to time with...

Lock wait timeout exceeded; try restarting transaction

I tried reducing the limit to 100, with the same error.

For information, the table as the following indexes :

$> SHOW INDEX FROM actions;

+-------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| actions |          0 | PRIMARY                   |            1 | id           | A         |      952794 |     NULL | NULL   |      | BTREE      |         |               |
| actions |          1 | account_uuid              |            1 | account_uuid | A         |         696 |     NULL | NULL   | YES  | BTREE      |         |               |
| actions |          1 | api_token                 |            1 | api_token    | A         |         848 |     NULL | NULL   | YES  | BTREE      |         |               |
| actions |          1 | ix_actions_code       |            1 | code         | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| actions |          1 | ix_actions_executed   |            1 | executed     | A         |      952794 |     NULL | NULL   |      | BTREE      |         |               |
| actions |          1 | ix_actions_is_treated |            1 | is_treated   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Is there a way to optimize the query to be run processed faster ?

I was wondering if doing something like the follow would not be faster.

UPDATE actions
SET is_treated = 1
WHERE id IN
(
    SELECT id
    FROM actions
    WHERE code = 200
        AND is_treated = 0
        AND account_uuid = :uuid
        AND DATE(executed) = UTC_DATE()
    LIMIT 1000
);

What do you recommend?

Thank you in advance.

18
  • You know you can have more than one column in an index, right? You probably also know that too many indexes on a table hurt update performance.
    – mustaccio
    May 19 at 11:13
  • Not really. I must confess I'm not that great on handling database performance (hence my request here). Things aren't as obvious for me, sorry. I hesitate between dropping "code" and "is_treated" (based on their cardinality) - or - dropping is_treated, code and executed, and creating one index for all three. But I'm not sure which one is the best
    – Cyril N.
    May 19 at 11:19
  • Guessing that is_treated, account_uuid, and DATE(executed) are all pretty relaxed filters on their own but together reduces the data set quite a lot? In that case, you want one index that covers all 3 columns. The waiting for lock is probably because the index it is using is leading it to get locks on rows that are already locked - the DATE(executed) = UTC_DATE() looks like it is targeting rows for today - ie the ones that are currently being inserted. May 19 at 11:44
  • Is it good if I keep the index for account_uuid, date, and create a new one with the combination ?
    – Cyril N.
    May 19 at 12:44
  • Welp I tried that, but I still have the same issue ...
    – Cyril N.
    May 19 at 13:07

1 Answer 1

1

Sargable

If the datatype of executed is DATE, then remove the function call. If it is TIMESTAMP or DATETIME, then change the test to:

    executed >= UTC_DATE()
AND executed  < UTC_DATE() + INTERVAL 1 DAY

In addition, you need this composite index:

INDEX(account_uuid, code,  -- in any order
      executed)            -- last

This index should significantly speed up the UPDATE.

(I am guessing that only one row is affected??)

1000

Are you looping through a lot of rows, doing 1000 at a time? How long is each iteration taking? Decrease the "1000" so that it does takes only a few seconds.

Building good index: Index Cookbook

In almost all cases, MySQL will use only one index in one [sub]query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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