I am seeing a query pattern that triggers a ~4000x performance regression from 5.7.25 to 8.0.13 using MyRocks. For the attached slow query, 5.7 is scanning 254 rows in 10 ms, but 8.0 is scanning 8.7M rows in 43 seconds. This is for the exact same data, the exact same query and the exact same my.cnf settings.
There are 182M rows in the table, which takes up 4 GB of disk space. Here is a link to a mysqldump of the data (1.5 GB) https://storage.googleapis.com/nozzle/linkmetrics_pinterest.sql.gz
This is a single representative query that reliably triggers the slowdown, but we have seen similar performance across many servers and tables. What seems to be common across all of them is that:
- it involves a range query
- the larger the table, the fewer where clauses it takes to trigger the slowdown
I have also included a shorter version of the query (normal-query.sql) that uses the same pattern, but runs at the same speed as 5.7.25.
Here is the side by side explain output:
How to reproduce inside the two docker images: