Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – A simple update query to a table in my database is taking after selecting 1 record is taking more than 1 mintute

This query is taking more than 90 seconds

UPDATE `tableA`
SET
    `bill_no` = NULL
WHERE 
`tableA`.`payment_no` = 'S00005303';

how to find the cause of slowness

I checked show processlist; and there are no other queries running at the same time.

Tags:

2

Answers


  1. 0

    The cause of slow performance you are experiencing could be caused by a number of things, which includes:

    • Your tableA table is very large, and MySQL is scanning the entire table to find the one row with a payment number of S00005303
    • There happened to be a large number of other queries/processes executing on MySQL when you ran the update. Due to limited bandwidth, MySQL took a long time to complete the update.

    If your application/use case would involve running this update often, you could try adding the following index:

    CREATE INDEX idx ON tableA (payment_no);

    This index, if used, would allow MySQL to rapidly find any records with a certain payment number, and make the update. To check if the index would be used, run:

    EXPLAIN ANALYZE
SELECT *
FROM tableA
WHERE payment_no = 'S00005303';
    Login or Signup to reply.
  2. 0

    What kind of database do you use? Is tableA indexed?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search