id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31965	MySQL fast-delete optimizations introduced in Django 3.1 don't properly deal with aggregation and cause performance regression.	Simon Charette	Simon Charette	"[https://github.com/django/django/pull/11996#issuecomment-682349899 As reported on Github] the fast-delete optimization on MySQL to use the proprietary `DELETE FROM` syntax introduced in #23576 and the follow up commit to disable it on MariaDB 10.3.1+ 5b83bae031a9771d86933bcc808d4bf2e21216a2 are causing performance regression due it's poor query planing of `DELETE` involving subqueries.

[https://github.com/django/django/pull/11931#issuecomment-547862119 While investigating the initially reported failure on MariaDB] I noticed that the MySQL's `SQLDeleteCompiler` implementation was also not properly dealing while filters against aggregation. It's normally not an issue because aggregation is done against against many-to-many relationships that involve reverse `ForeignKey` that usually have an `on_delete` that require in-memory fetching from `deletion.Collector` (e.g. `CASCADE`, `SET_NULL`) and thus disable fast-delete. However when only fast-delete `on_delete` handlers are involved (e.g. `DO_NOTHING` or future database level `on_delete` support #21961) this can be an issue that results in a `Invalid use of group function` crash due to an attempt at using aggregate functions in the `WHERE` clause.

In order to address both issues I suggest we
1. Always use the subquery approach when aggregation is involved
2. Materialize the subquery at the database level when the backend doesn't have the `update_can_self_select` feature
3. Favor the proprietary `DELETE FROM` syntax otherwise to work around MySQL poor handling of subqueries and avoid the generation of slow queries.
"	Bug	closed	Database layer (models, ORM)	3.1	Release blocker	fixed			Accepted	1	0	0	0	0	0