-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

New contributor
Fernando Santiago is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 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
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
New contributor
Koteswara Rao is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

Fernando Santiago is a new contributor. Be nice, and check out our Code of Conduct.

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.