-1

I have the next query:

UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND branch_id IN
(23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,218,220,221,222,223,224,226,231,232)

Here is my plates_owners structure:

plate_id     | int(11) unsigned | PRI
branch_id    | int(11) unsigned | PRI
show_app     | int(1) unsigned  |
deleted      | int(1) unsigned  |
app_disabled | int(1) unsigned  |
modified     | timestamp        |

This query takes over 50 seconds to execute, is there anyway to optimize it?

PS: The less values I add to the IN statement the faster the query becomes

  • do you have any indexes on (plate_id,branch_id) (using both columns)? What does PRI mean in your "structure of the table"? Can you give a SQL command how you created the table? – arthur Feb 14 at 9:20
  • How many rows in the table? How many have deleted=1? – Rick James Feb 21 at 19:20
  • Which order are the columns in the PRIMARY KEY? Please provide SHOW CREATE TABLE plate_owners to avoid questions like this. What version of MySQL? Things changed recently. – Rick James Feb 21 at 19:24
0

You could try creating yourself a temporary table, inserting your branch numbers into it, and then performing the UPDATE using a JOIN, like so:

CREATE TEMPORARY TABLE branch_numbers 
(
    id INT NOT NULL PRIMARY KEY
);
INSERT branch_numbers (id)
VALUES (23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36)
      ,(37),(38),(39),(40),(42),(43),(46),(47),(48),(64),(79),(82),(83),(102)
      ,(103),(130),(169),(209),(218),(220),(221),(222),(223),(224),(226),(231)
      ,(232);

UPDATE plates_owners 
 INNER JOIN branch_numbers ON plates_owners.branch_id = branch_numbers.id
   SET deleted=1 
 WHERE plate_id=4748;

DROP TABLE branch_numbers;
  • CREATE TEMPORARY TABLE branch_numbers (branch_id INT NOT NULL, plate_id INT NOT NULL DEFAULT 4748, PRIMARY KEY(plate_id, branch_id) ); is more safe - both indices will match fully. – Akina Feb 14 at 5:20
0
UPDATE plates_owners
SET deleted=1 
WHERE plate_id=4748
  AND FIND_IN_SET(
         branch_id,
         (23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,218,220,221,222,223,224,226,231,232)
      ) > 0
  • The second argument needs to be a string, and without the parens. – Rick James Feb 21 at 19:19

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.