After some experimenting I came up with this mockup (I tested it on MariaDB 10.3):

select * from (
        select * from revision
        where rev_page = 248348
        order by rev_id desc
        limit 15
    ) revs_filtered
    where rev_sha1 = 'civ8b9x9x0xbvyvy8cnr5a9fle2cdqc';

This will select the 15 most recent revisions and then check if any of them has the SHA-1 we're looking for, so the scan is limited to 15 rows. This particular page has over 1500 revisions.
EXPLAIN for this query:

+------+-------------+------------+------+------------------------------------------------+-------------+---------+-------+------+-------------+
| id   | select_type | table      | type | possible_keys                                  | key         | key_len | ref   | rows | Extra       |
+------+-------------+------------+------+------------------------------------------------+-------------+---------+-------+------+-------------+
|    1 | PRIMARY     | <derived2> | ALL  | NULL                                           | NULL        | NULL    | NULL  |   15 | Using where |
|    2 | DERIVED     | revision   | ref  | rev_page_id,page_timestamp,page_user_timestamp | rev_page_id | 4       | const | 1556 | Using where |
+------+-------------+------------+------+------------------------------------------------+-------------+---------+-------+------+-------------+

And, according to MySQL manual:

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast.

In this case we do have a key like this, rev_page_id that consists of rev_page and rev_id. Sooo I think we should be fine here, we have an index scan, then an index-based sort operation limited to N rows and then a table scan on a small number of rows.

Correction: we should order revisions by timestamp, not by their IDs (which can do all kinds of crazy things). So the order clause should look like order by rev_timestamp desc.

The conclusion though is the same: we still have an index that would suit this use case, page_timestamp.

Change 607855 had a related patch set uploaded (by Ostrzyciel; owner: Ostrzyciel):
[mediawiki/core@master] WIP: EditResultBuilder: manual revert detection

https://gerrit.wikimedia.org/r/607855

I just found T216297 which contains an interesting discussion on this topic. One thing in particular made me curious:

In T216297#5035877, @daniel wrote:

Note that manual reverts (by clicking edit on an older revision, and then saving) is detected by hash, but the "original rev ID" is not set in this case. This could be done without too much trouble, but if I recall correctly, this may confuse some extensions.

@daniel, do you remember what extensions could be affected by such an approach? Or do you have some hints on how to find them? Currently my plan is to do just that, I'll be setting the original rev ID to the edit a user manually reverted to. I had hoped this wouldn't break anything :D

Change 609242 had a related patch set uploaded (by Ostrzyciel; owner: Ostrzyciel):
[mediawiki/core@master] Add mw-manual-revert change tag

https://gerrit.wikimedia.org/r/609242

Change 607855 merged by jenkins-bot:
[mediawiki/core@master] EditResultBuilder: manual revert detection

https://gerrit.wikimedia.org/r/607855

Change 609242 merged by jenkins-bot:
[mediawiki/core@master] Add mw-manual-revert change tag

https://gerrit.wikimedia.org/r/609242

Change 613801 had a related patch set uploaded (by DannyS712; owner: Shirayuki):
[mediawiki/core@master] PageUpdater: Update @since tag from 1.35 to 1.36 for $wgManualRevertSearchRadius

https://gerrit.wikimedia.org/r/613801

Change 613801 merged by jenkins-bot:
[mediawiki/core@master] PageUpdater: Update @since tag from 1.35 to 1.36 for $wgManualRevertSearchRadius

https://gerrit.wikimedia.org/r/613801

As a bot owner, I would like to mention several situations where the "manual revert" tag is applied in a situation where it doesn't seem relevant. The tag itself doesn't do much harm, but as far as I understand, there is a plan to notify users from those manual reverts (T154637), so this could become more annoying.

• Requests pages: when a bot archives requests that have been processed (e.g. https://fr.wikipedia.org/w/index.php?title=Wikip%C3%A9dia:Demande_d%27intervention_sur_un_message_syst%C3%A8me&diff=174930164&oldid=174843790). If the last X requests are marked as done and archived, this is seen as a "manual revert" of all edits done on those requests.
• Lists of things to do: I maintain pages with lists of pages to fix, such as https://fr.wikipedia.org/w/index.php?title=Projet:Restauration_lien_rouge/par_distance/Q&action=history. Typically, the bot adds pages to the list every X days and then and users remove items that they fix, occasionally "reverting" to the state where the list is empty. In that case, the notification is triggered by humans.
• Removing a protection template once the protection has expired (https://fr.wikipedia.org/w/index.php?title=Histoire_du_Sahara_occidental&diff=prev&oldid=174948749). Admittedly, it makes sense in that case to tag it as a revert, but still, I don't think sysops who add the template manually would want to get notified of a revert when my bot removes the template.

Will there be a way to exclude some users or pages from that mechanism?