MySQL - terrible performance for simple query on large table I'm having huge troubles with the performance of a very simple indexed query on a quite large table.
Configuration:

48 cores, NVMe Raid, 376GB RAM (144GB used)
Mysql 8 (Percona server)
Server dedicated to DB
Application with low concurrent users
InnoDB
Overall database size: ~1TB
Table size: ~35GB
Table structure:
primary_key (int),
indexed_field (int),
value (int),
plus a few other ints, dates and a varchar

The query: SELECT AVG(value) WHERE indexed_field=[somevalue] =&gt; takes 220s for ~2M rows
Here is the associated explain

I tried the following other queries to try and understand what was going on:

SELECT COUNT(*) WHERE indexed_field=[somevalue] =&gt; 1s (~2M rows)
SELECT AVG(primary_key) WHERE indexed_field=[somevalue] =&gt; 1s (~2M rows)
SELECT AVG(value) WHERE indexed_field=[anothervalue] =&gt; 0s (~15k rows)

I understand the first two use only the index without needed access to the actual data, which makes them super fast ; and the third one is also super fast thanks to the low amount of rows selected by the indexed_field.
What I can't figure out is why the initial query is so slow - MySQL is perfectly capable of processing millions of rows almost instantly (I just tried an AVG(random_field) on a 3M rows table and got the result in 3 seconds), I do provide it with the appropriate index to find them, so why does it take so long?
Here are also a few values out of the mysql config:

sort_buffer_size = 4M
key_buffer_size=48G
innodb_buffer_pool_size=96G
max_heap_table_size=12G
tmp_table_size=12G
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