MySQL - terrible performance for simple query on large table
key_buffer_size=48G  -- lower to 50M unless you are using MyISAM tables
innodb_buffer_pool_size=96G  -- why not 300G
max_heap_table_size=12G  -- limit to 1% of RAM
tmp_table_size=12G       -- ditto

Queries 2 and 3 are &quot;covering&quot;, as you paraphrased with &quot;use only the index without needed access to the actual data&quot;.
Queries 1 and 4 are the same, except for the number of rows involved.
Did you run the first query twice?  Probably the first time, the necessary data blocks were not cached in the buffer_pool and needed to be fetched from disk.  In an extreme case, that could be 2M disk reads.  Even NVMe probably takes more than 220s to do 2M reads.
If you have a lot of &quot;average&quot; and similar queries, consider building and maintaining &quot;summary tables&quot;.  They can make things significantly faster for huge tables.  http://mysql.rjweb.org/doc.php/summarytables