MySQL update query optimization - subqueries or not?

Question

Which query will be the fastest: my own tests show no obvious results:

QUERY1:

UPDATE items, brands SET items.brand_id = brands.id WHERE brands.name = 'apple' 

- vs -

QUERY2:

 UPDATE items SET brand_id = (SELECT id FROM brands WHERE name = 'apple') 

I can not find any data about this on Google; Perhaps some SQL experts know the answer?

Looking only at the syntax, I personally prefer the first. While the others that I'm talking about prefer the second (in order to be more obvious)?

Answer 1

I would expect them to work equally fast, but running tests on ~ 4M innoDB table files shows the following results:

 mysql> update t, (select now() value) onerow set update_date = onerow.value; Query OK, 3999960 rows affected (2 min 12.32 sec) Rows matched: 3999960 Changed: 3999960 Warnings: 0 mysql> update t set update_date = (select now()); Query OK, 3999960 rows affected (1 min 28.66 sec) Rows matched: 3999960 Changed: 3999960 Warnings: 0 

(the current test for the second time was 2 min 11.52 s and 1 min 26.67 s, respectively)

The reasons can be in different ways: mysql considers updates of one table and updates of several tables, see documents .

Note: while it reads about how mysql handles UPDATE - it has some terrible deviations from the SQL standard (it is sensitive to the assignment order, which is not even consistent between updating a single table and multiple updating a table - with multiple updating the table in mostly not deterministic in expressions such as UPDATE t SET column1=column1+100, column2=column1 )

Answer 2

The first request will effectively cross-connect before the upgrade, which is terrible performance.

The second query will run this subquery for each row in the external table, which is very poor performance.

Nothing is especially good ... just the thing is worse. :)

Do you want to update the entire items table?

Have you tried searching for @ EXPLAIN equivalent SELECT two queries?

 EXPLAIN SELECT items.brand_id, brands.id FROM items, brands WHERE brands.name = 'apple' 

and

 EXPLAIN SELECT brand_id, id FROM items, (SELECT id FROM brands WHERE name = 'apple') a 

Even better, run UPDATE / equivilent SELECTS and do a few SHOW STATUS LIKE 'handler_%' to see how many lines are being read / written.