0

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

enter image description here

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 enter image description here

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

1
  • You should try to make a temp table with your data and join it with the update table and update the data. Also, it could depend if you have foreign keys. Apr 28, 2022 at 7:15

2 Answers 2

0

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));
0

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.

4
  • This will calculate a new queue number for each entry where he wants them to be the same.
    – Flourid
    Apr 28, 2022 at 15:24
  • @Flourid - I need some more context on "he" and "them" and "same".
    – Rick James
    Apr 28, 2022 at 15:30
  • You query will calculate a new queue number for each entry, but OP wants a queue number for all entries with the same club_id and user_id. Quote "My aim is to update the column queue_id, of rows with same (club_id,user_id) to same number"
    – Flourid
    Apr 28, 2022 at 15:37
  • @Flourid - Well, I addressed your Comment, but I am not more confused. Maybe you can take my idea and build a better Answer?
    – Rick James
    Apr 28, 2022 at 15:51

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.