Mysql – situation where MySQL creates a nonclustered index automatically

Since we are talking about the clustered index, just because you defined the CI key column as ID, you still have the DeletedDate data in the leaf data pages of the index. That's the nature of the clustered index: It is the table data.

Because you are typically having queries that look like:

select *
from YourTable
where DeletedDate is null;

You will likely benefit from a filtered index.

create nonclustered index IX_YourFilteredNci
on YourTable(<Key Columns Here>)
where DeletedDate is not null;
go

I didn't explicitly put the key columns here (and nonkey columns through the use of the INCLUDE clause) because you didn't publish the DDL of your table.

As in my comment above to your question, the choice of key columns (not just columns, but also the order of the columns) will largely depend on your workload and the typical queries that would be using this index.

If you are looking to cover your query(ies), then you would need to ensure that the index satisfies all of the data required of the query(ies). Not to mention, if you have other WHERE clauses (besides your NULL check on DeletedDate) or joins to consider, then the order of your key columns can be the deciding factor between a scan or a seek. And even though it is filtered, and depending on how much data you have in the index, the penalty could be considerable.

InnoDB: The PRIMARY KEY is UNIQUE and clustered -- always. Secondary keys are non-clustered. They have the PRIMARY KEY (not a rownum or rowid) in the leaf node of the secondary key index.

MyISAM: Keys are non-clustered -- always. The leaf nodes of indexes have a pointer (think: byte offset) into the data file to get from the index to the data. ALTER TABLE tbl ORDER BY ...; will reorder the "data", thereby temporarily give you clustering on whatever you ORDERed BY.

MySQL decided those were "good enough". I have only very rarely found a need for something else.