The UPDATE queries is 50x slower than its SELECT query



  • 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



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2