Search code examples
mysqlsqlquery-optimization

how to rewrite the update query?

I don't have the EXPLAIN plan for the below query but the where condition is using a primary index. but it takes so much time to execute kindly suggest better option to increase the performance.

UPDATE `customer_entity` 
SET `website_id` = ?, `email` = ?, `group_id` = ?, `store_id` = ?,
    `created_at` = ?, `updated_at` = ?, `disable_auto_group_change` = ?, 
    `firstname` = ?, `lastname` = ?, `password_hash` = ?, `rp_token` = ?, 
    `rp_token_created_at` = ?, `confirmation` = ?, `gender` = ?,
    `failures_num` = ?, `first_failure` = ?, `lock_expires` = ?,
    `contact_no` = ?, `alt_mobile_no` = ?, `employee_code` = ?,
    `mobileapp_customer_id` = ? 
WHERE (`entity_id`=?)
Solution

  • I would debug that query this way:

    1. Try to run the EXPLAIN to see the estimated execution plan. You say you are not able to do this - that's a shame, because then you are a bit more in the dark.

    2. Run SHOW PROCESSLIST while this query is running. There might be some insights here as to what is going on. Is the database server completely flooded? Is the query in question waiting for another lock on the table?

    3. Check performance metrics on the server. If you are using a managed database via a cloud provider, they typically have statistics that shows CPU, Memory, Disk IO usage etc. It might show that your database is forced to its knees.

    4. In MySQL, writes (this includes UPDATE-statements) are slowed down if the table has many indexes. Does the table contain more indexes than usual?

    5. Run FLUSH STATUS -> [Run you query] -> SHOW STATUS to see if the status variables include valuable information about the query just run. The EXPLAIN tells you what the estimated query execution plan is. STATUS tells you what the actual execution just did.

    I do not think that an update by primary key would take 39 minutes no matter how many rows there is, so I would look for other clues.