MySQL Update Query Optimization - Subqueries or not?

Which query would be the fastest: My own tests do not show any 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't find any data on this with Google; maybe some SQL experts here know the answer?

Looking just at the syntax I personally prefer the first. Whilst others I speak prefer the second (for being more obvious)?

I would expect them to run equally fast, but running tests on ~4M records innoDB table 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

(running test the second time resulted in 2 min 11.52 sec and 1 min 26.67 sec, respectively)

The reasons might be in the different way mysql treats single table updates and multi table updates, see docs.

Note: while at it read about how mysql treats UPDATE - it has some horrible deviations from SQL standard (it is sensitive to order of assignment, which is not even consistent between single table update and multiple table update - with multiple table update basically being non deterministic in statements such as UPDATE t SET column1=column1+100, column2=column1)

The first query will effectively do a cross-join before the update, which it the terrible performance wise.

The second query will run that subquery for every row in the outer table, which is very bad performance wise.

Neither is particularly good... just a matter of which is worse. :)

Do you mean to be updating your entire items table?

Have you tried looking @ the EXPLAINs of the equivilent SELECTs of the 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 yet, run the UPDATEs / equivilent SELECTSs and do some SHOW STATUS LIKE 'handler_%' to see exactly how many rows are being read/written.