MySQL not possibly picking up correct index?

Question

I am working with MySQL with this table. Here is the schema.

create table if not exists users
(
    id            bigint unsigned auto_increment
        primary key,
    created_at    datetime(3)  null,
    updated_at    datetime(3)  null,
    deleted_at    datetime(3)  null,
    email         varchar(255) not null,
    password_hash varchar(191) not null,
    constraint idx_users_email
        unique (email)
);

create index idx_email_pass
    on users (email, password_hash);

create index idx_users_deleted_at
    on users (deleted_at);

When I run this query

EXPLAIN SELECT * FROM `users` WHERE `users`.`email` = 'name@email.org' AND `users`.`password_hash` = 'anewpassword' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1

(This query, and the create table/index query and all that is generated by golang/gorm package, which is my ORM.)

The result is

+----+-------------+-------+------------+-------+-----------------------------------------------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys                                       | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------------------------------------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | idx_users_email,idx_users_deleted_at,idx_email_pass | idx_users_email | 1022    | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+-----------------------------------------------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

This possibly means that it is not utilizing the specialized and more appropriate index idx_email_pass, in favor of idx_users_email.

Is the gorm-added query field deleted_at IS NULL is the one creating problems? I don't think so.

Why? I tried EXPLAIN <query> without deleted_at IS NULL part, and it still picked the same index.

I created an index idx_email_pass_del, but that didn't get picked with the original query either.

---

So I want to understand the following:

1. Will the correct index utilized when data grows larger? I guess I should say that at this point, there are like 5 rows in database.

2. Are two separate indexes (A,B,C) and D index sufficient for a query WHERE A=a AND B=b AND C=c AND D IS NULL?

Answer 1

As you have a unique constraint on email the index on the email plus other columns is pointless - as both indexes will have an identical number of rows.

As the unique constraint index is smaller the optimiser will almost always prefer it over the other index