A Django's Auth table's indexes:
mysql> show index from auth_user;
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| auth_user | 0 | PRIMARY | 1 | id | A | 130 | NULL | NULL | | BTREE | | |
| auth_user | 0 | email | 1 | email | A | 130 | NULL | NULL | | BTREE | | |
| auth_user | 0 | username | 1 | username | A | 130 | NULL | NULL | | BTREE | | |
| auth_user | 1 | is_active | 1 | is_active | A | 2 | NULL | NULL | | BTREE | | |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)
where a search by the exact email
select * from auth_user where email = 'foo@bar.com';
results in:
const search on the email index
const tables are very fast because they are read only once.*
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | auth_user | NULL | const | email | email | 227 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
While a prefix scan using like (it's done by default in django admin panels)
select * from auth_user where email LIKE 'foo@bar.com';
results in:
a range search used on the email index. Using Index Condition Push down optimization** (ICP).
ICP conditionally reads a table row to match other conditions in the where clause iff the indexed column is a match.
In search by the exact email case, it's almost equivalent to the const search above. In both cases the whole index was scanned.
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | auth_user | NULL | range | email | email | 227 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
The edge belongs to the exact match search which uses const tables.
References:
*MySQL's - Explain output;
**Index Condition Push Down Optimzation;
