CODEWITHSUNDEEP
sqlmysql
Bob the Knob
Bob the Knob

Reputation: 41

Optimizing a MySQL query (Update + subquery)

I want to change the values of column this in table_one where this = 1 and id value of that row exists anywhere in table_two's column other_id

So I'm using this query:

UPDATE table_one SET this='0' WHERE this='1' AND table_one.id IN (SELECT other_id FROM table_two);

The query took several hours without yet finishing, so I was wondering if there is a way to accomplish it faster.

Both table_one.id and table_two.other_id have unique values if that matters.

table_one has 550k rows

table_two has 30k rows

MySQL 5.0.22

Upvotes: 1

Views: 448

Answers (4)

Bob the Knob
Bob the Knob

Reputation: 41

Actually the solution was to add indexes for both table_one.id and table_two.other_id. I mentioned they're unique, but I didn't mean that they have indexes, so my bad for not clarifying that.

After adding the indexes the query took less than a minute.

Also replacing IN with EXISTS gave me a syntax error, so I stuck with IN.

Upvotes: -1

Mike
Mike

Reputation: 21659

An UPDATE...JOIN is an alternative to the EXISTS answer provided by tuffkid and Brian Hooper. I've tested both, and they both take the same time (2.52 seconds on my computer):

UPDATE table_one
JOIN table_two
  ON table_one.id = table_two.other_id
SET this=0
  WHERE table_one.this=1;

The joined columns should be indexed, and if table_two.other_id has a UNIQUE index, the query should run very fast.

Example

Create tables:

CREATE TABLE `table_one` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `this` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `this` (`this`)
);

CREATE TABLE `table_two` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `other_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `other_id` (`other_id`)
);

Insert some test data:

DELIMITER //
DROP PROCEDURE IF EXISTS populate//
CREATE PROCEDURE populate()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 550000 DO
        INSERT INTO table_one (this) VALUES ((i MOD 18)+1);
        IF i MOD 18 = 0 THEN
            INSERT INTO table_two (other_id) VALUES (i+1);
        END IF;
        SET i = i + 1;
    END WHILE;
END;
//
DELIMITER ;

CALL populate();

Run the update:

UPDATE table_one
JOIN table_two
  ON table_one.id = table_two.other_id
SET this=0
  WHERE table_one.this=1;

Query OK, 30556 rows affected (2.52 sec)
Rows matched: 30556  Changed: 30556  Warnings: 0

UPDATE table_one
  SET this=0
  WHERE this=1 AND
    EXISTS (SELECT other_id
      FROM table_two
      WHERE other_id = table_one.id);

Query OK, 30556 rows affected (2.52 sec)
Rows matched: 30556  Changed: 30556  Warnings: 0

Upvotes: 1

Brian Hooper
Brian Hooper

Reputation: 22054

To expand upon tuffkid's answer...

UPDATE table_one
    SET this='0'
    WHERE this='1' AND
          EXISTS (SELECT other_id
                      FROM table_two
                      WHERE other_id = table_one.id);

Upvotes: 0

tuffkid
tuffkid

Reputation: 1323

try using EXISTS instead of IN

Upvotes: 1

Related Questions