The UPDATE queries is 50x slower than its SELECT query

Question

The UPDATE queries is 50x slower than its SELECT query.

I have a table named sync_read and the have the following columns

id
club_id
created_at
user_id
queue_id

The table have the following Indexes:

Keyname            Column
club_id            club_id  
user_id            user_id
club-contact       (club_id,user_id)  

There are around 69K records in that table. My aim is to update the column queue_id, of rows with same (club_id,user_id) to same number like

id | club_id | created_at | user_id | queue_id
1     99        2015-05-05   8994       59294
2     45        2015-05-05   9872       892191
3     99        2015-05-04   8994       59294

I am using the below query to update the data

UPDATE sync_read,
       (SELECT GROUP_CONCAT(id) AS ids,(FLOOR(RAND() * POW(10,6))) AS rand 
       FROM sync_read 
       WHERE club_id = 15085 AND created_at = '2022-03-11 18:50:51'
       GROUP BY club_id, user_id)
        AS grouped
SET sync_read.queue_id = grouped.rand
WHERE sync_read.created_at = '2022-03-11 18:50:51'
  AND sync_read.club_id = 15085
  AND FIND_IN_SET(sync_read.id, grouped.ids) ;

76 rows affected. (Query took 22.2668 seconds.)

This seems to be very slow. So I checked the EXPLAIN for that query. The result is like this

Then I tried to select the same data

SELECT * FROM sync_read, 
    (SELECT GROUP_CONCAT(id) AS ids,(FLOOR(RAND() * POW(10,6))) AS rand 
    FROM sync_read 
    WHERE club_id = 15085 AND created_at = '2022-03-11 18:50:51' 
    GROUP BY club_id, user_id) AS grouped 
    WHERE sync_read.created_at = '2022-03-11 18:50:51' AND sync_read.club_id = 15085 AND FIND_IN_SET(sync_read.id, grouped.ids)

Showing rows 0 - 24 (76 total, Query took 0.5225 seconds.)

The result for the EXPLAIN is

So you can see that the SELECT is taking only 0.5 sec, but the UPDATE on the same number of rows took 25sec. Why is the UPDATE query considerably slower than the SELECT statement. After seeing the EXPLAIN result I doubt if the MySQL is considering the index correctly

Answer 1

This might not be the answer you are looking for, but why not use a composite key for the queue_id, generated from club_id and user_id? You can even add it to your table like this:

alter table sync_read add column gen_queue_id bigint as (concat(club_id,user_id));

Answer 2

It seems like this would do the job much faster:

UPDATE sync_read
    SET sync_read.queue_id = FLOOR(RAND() * POW(10,6))
    WHERE club_id = 15085
      AND created_at = '2022-03-11 18:50:51';

That needs

INDEX(club_id, created_at)   -- in this order

But, does queue_id belong in that table??

Redundancy of queue_id

Meanwhile, "My aim is to update the column queue_id, of rows with same (club_id,user_id) to same number" sounds like you have queue_id repeated across multiple rows. Remove that column from this table, and put it into a separate table so that it is not repeated. That table would have 3 columns: (club_id, user_id, queue_id), with the first 2 being the PK.

That may require a JOIN in some other queries.