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
25399	Improve performance for user lookups by adding database index	SimonSteinberger	nobody	"Having a user database table with millions of entries, even a simple look-up by username (or email address) takes a rather long time and causes a significant overhead on servers. For example, the Django database query for a user signing in looks roughly like this:

    SELECT * FROM ""accounts_user"" WHERE UPPER(""accounts_user"".""email""::text) = UPPER('foo@bar.com')

The problem is, without a database index, the string transformation and comparison takes a lot of resources and even with a powerful infrastructure, such a query may take over a second when having a million or more users. With the growing user base, the query gets slower an slower.

By adding a database index, the process takes only about a microsecond. Using PostgreSQL, we've simply added the index manually, which works fine:

    CREATE INDEX accounts_myuser_username_upper ON  accounts_myuser (UPPER(username));

Yet, this is a crucial part of most web applications and therefore, it would probably make sense if Django created the index automatically for the integrated User and AbstractUser classes. Or at least, the issue could be pointed out clearly in the docs.
"	Cleanup/optimization	new	contrib.auth	1.8	Normal		user, database, index		Unreviewed	0	0	0	0	0	0