Optimizing a MySQL query (Update + subquery)

Question

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

Answer 1

try using EXISTS instead of IN

Answer 2

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);

Answer 3

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

Answer 4

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.