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
31276	Add 'flush' option to only clear non-empty tables	Adam Johnson	jonatron	"This was suggested as an extra feature for Django-MySQL by @jonatron in ​https://github.com/adamchainz/django-mysql/pull/611/files , but it would make more sense to make it in Django core.

The `sql_flush` database operation, used by the 'flush' management command and, thus through `TransactionTestCase`, creates one `TRUNCATE TABLE` statement for every table - regardless of whether they are empty already. This means unnecessary database operations are run, which will be most noticeable in test runs.

Instead of flushing *every* table, we can flush only the non-empty ones.

A minimal, hopefully cross-database way of quickly finding out which tables in a list are non-empty is to make existence queries on them combined with union:

{{{
chainz@localhost [14]> (select 't' as has_rows from t limit 1) union (select 't2' as has_rows from t2 limit 1);
+----------+
| has_rows |
+----------+
| t2       |
+----------+
1 row in set (0.001 sec)
}}}

We could use logic like this to batch the tables into such union queries in `django.core.management.sql.sql_flush`. We could then reduce the list of tables we wish to flush down to the non-empty ones for the `sql_flush` operation. Perhaps it would be best to put this behind a flag that is set only in tests, for backwards compatibility."	Cleanup/optimization	closed	Database layer (models, ORM)	dev	Normal	wontfix			Accepted	1	0	0	0	0	0