MYSQL: How to optimize a date sorted table with a triple primary key index
Your query is not sargeable. Using Hour() function on the first column (pk1) of the composite primary key is inhibiting the usage of the Primary Key for index lookup; and thus (in absence of any other proper index), it is most likely doing full table scan. The general rule of thumb to follow while defining a working index is:


First priority should be given to all the columns, which are inside the WHERE clause, and are connected by AND clause, and are compared to a constant value using either =, IS NULL, or &lt;=&gt;. So, in your query, there are two columns following this: Data_Type and Status.
Second priority can be given to following three scenarios:


Column having Range condition.
Columns in a specific order in the GROUP BY clause (if existing).
Columns in a specific order in the ORDER BY clause (if existing)



Now, coming to your questions:


  Would changing the DATETIME to a DATE (and thus remove HOUR(0), no
  longer needed) resolve the problem in a satisfying enough way ? I
  don't need it anymore, especially if it causes problems.


Yes, you should do that; if the time part is irrelevant to you. In MySQL 8+, the size of datetime is 5 bytes, while the size of date is 3 bytes. So you are effectively going to save considerable amount of space as well.

With this change, your new query would simply be:

SELECT * FROM table 
WHERE pkey2 = ... 
  AND pkey3= ... 
  -- removed AND HOUR(pk1) = 0 
ORDER BY pkey1 DESC


In this scenario, if you refer the thumbrule discussed above, you need to define a new Composite Index (pkey2, pkey3, pkey1) (the ordering is changed; you will need to keep this index besides the already defined Primary Key).

ALTER TABLE table_name ADD INDEX (pkey2, pkey3, pkey1);


This should be able to help you considerably.