#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage: Accepted
race-condition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
I confirmed that the Python side materialization of the query can be
entirely avoided by using the same strategy as #31965
(f6405c0b8ef7aff513b105c1da68407a881a3671).
While MySQL disallows `UPDATE table ... WHERE table.id IN (SELECT id FROM
table WHERE ...)` it allows `UPDATE table ... WHERE table.id IN (SELECT *
FROM (SELECT id FROM table WHERE ...) subquery)` as it materialize the
subqueries remotely.
I also learned that MySQL `UPDATE`
[https://www.sqlite.org/lang_update.html#update_from_in_other_sql_database_engines
supports updating multiple table at a time which is wild] so I figured I'd
give a shot making use of it.
Not sure if this changes the outcome of this ticket but
[https://github.com/django/django/compare/main...charettes:django:mysql-
update-tweaks the first two commits of this branch seem to address the
issue entirely] by
1. Materializing the subquery on the server side under all circumstances
2. In cases where the `UPDATE FROM` syntax can be used with `JOIN`s to
avoid the materialization of a subquery (which MySQL is notably bad at)
adopt a similar strategy to #23576
(7acef095d73322f45dcceb99afa1a4e50b520479). That's possible when no
aggregation or related updates are used.
The last commit is complementary but demonstrate an optimization for MTI
updates of the form
{{{#!python
Child.objects.filter(child_field=0).update(parent_field=1, child_field=2)
}}}
That are currently implemented through two N + 1 queries on all backends
{{{#!sql
SELECT parent_prt_id FROM child WHERE child_field = 0
UPDATE child SET child_field = 1 WHERE parent_prt_id IN :parent_prt_ids
UPDATE parent SET parent_field = 2 WHERE id IN :parent_prt_ids
}}}
But are replaced by the following on MySQL
{{{#!sql
UPDATE child
JOIN parent ON (child.parent_ptr_id = parent.id)
SET child.child_field = 1, parent.parent_field = 2
WHERE child_field = 0
}}}
Note that the optimization must be disabled if `order_by` is used against
inherited fields as MySQL doesn't allow references to `JOIN`'ed table in
`UPDATE ORDER BY` (the join clause would have to be altered to be an
ordered subquery).
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:8>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/010701954559d10d-6b8a0d72-704f-4a1a-91ad-0ea3074ca712-000000%40eu-central-1.amazonses.com.
