I'm having huge troubles with the performance of a very simple indexed query on a quite large table.
- 48 cores, NVMe Raid, 376GB RAM (144GB used)
- Mysql 8 (Percona server)
- Server dedicated to DB
- Application with low concurrent users
- 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
SELECT AVG(value) WHERE indexed_field=[somevalue] => takes 220s for ~2M rows
I tried the following other queries to try and understand what was going on:
SELECT COUNT(*) WHERE indexed_field=[somevalue]=> 1s (~2M rows)
SELECT AVG(primary_key) WHERE indexed_field=[somevalue]=> 1s (~2M rows)
SELECT AVG(value) WHERE indexed_field=[anothervalue]=> 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