Return to Revisions

1. Create calculated field

    HOURpkey1 TINYINT AS (HOUR(pkey1)) 
   

Make it either STORED or VIRTUAL - think by yourself what is better for you.

2. Create index

    KEY idx_pkey2_pkey3_HOURpkey1 ON table (pkey2,pkey3,HOURpkey1)
   

Any fields order is possible and safe for the query in question, think by yourself what is the best.

If the amount of records in output is huge and their (final) sorting is expensive, then add pkey1 into the index expression as the last, 4th, field.

OP will also need to change the query; instead of using HOUR(pkey1) = 0 to HOURpkey1 = 0 (refer the new generated column now)

That's wrong.

See fiddle - idx2 is used in both cases.