Bug #14152 Slow update, optimization is different from select
Submitted: 19 Oct 2005 20:34 Modified: 26 Nov 2005 6:43
Reporter: Andre Timmer Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:4.1.3 OS:Any (All)
Assigned to: CPU Architecture:Any

[19 Oct 2005 20:34] Andre Timmer
Description:
update table1, table2
set table1.col1 = ..
,    table1.col2 = ... 
where table1.id = table2.id;

Is slow, where as following select is fast:

   select * from table1, table2
   where table1.id = table2.id;

The documentation says that updates are optimized the same way as selects.
This is not true.

How to repeat:
What MySQL seems to be different that for an update the "driving" table is the first table.

So this gives a problem when there is an index on table1.id and not on table2.id.
(according to explain this is not necessairy)

Suggested fix:
Fix it.

Or as an alternative make explain of update, delete and insert possible so a developer can see what will be happening.
[20 Oct 2005 11:14] Valeriy Kravchuk
Thank you for a problem report.

What MySQL version do you use? If it is 4.1.13, please, upgrade and try newer, 4.1.14 or wait a couple of days for 4.1.15 to be officially released.

Please, send the SHOW CREATE TABLE results for both tables involved, as well as real update statements used (or just confirm those you already posted are used).
[20 Oct 2005 16:24] Andre Timmer
Statements posted are used.

After creating an index on table2.id the update was fast as well.
For the select it wasn't necessairy.

For the problem to be reproducable you need a table with a lot of rows.
[20 Oct 2005 17:50] MySQL Verification Team
Hi!

Thank you for writting to us.

This is a forum for fully reproducible test case. That is, we require a series of SQL statements that will clearly demonstrate the problem. If a table is large, you can upload it to this bug record.

Also, before doing that, please check whether problem is still present in 4.1.15 which was just released.
[25 Oct 2005 18:10] Andre Timmer
We will migrate to MySQL 5 first.
[26 Oct 2005 6:43] Valeriy Kravchuk
So, you decided to migrate to MySQL 5.0.15. Great! Please, reopen this bug report (with more information on how to repeat the problematic behaviour), as soon as you encounter similar problem with this version.
[27 Nov 2005 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".