Re: Slow query on partitioned tables with over 200M rows

[Rick James]

> Are you aware of performance benchmark results on table size vs query response time?

No. And I would be skeptical of them.

A BTree (the majority of indexes in MySQL), is O(log N). A million rows will have about 3 levels of BTree; a billion rows will have about 5. But in your query, there is only one descent into the BTree, after that, it is a linear scan.

So the number of blocks needed might be
5 -- drill down the BTree; most of these are probably cached; plus
100 -- to scan through about 20K records.
That's 5+100. For a smaller table it might be 3+100. Not much difference.

On the other hand, if you are scanning the same rows again, there is a caching effect. The 5+100 is for a 'cold' cache. If you promptly repeat the same query, none of those blocks will need to be read from disk. This will give you about 10x speedup.

> I would expect 360 rows per device and component on a partition.

So, the query that is timing out is touching only 360 'consecutive' rows? They would probably fit in only 2 blocks. I would expect the SELECT to take less than 0.1s, even with a cold cache. Something strange is going on. Are VIEWs involved? SHOW VARIABLES LIKE 'innodb%' -- I want to verify the 100G.

After a while (a long while), you will fill up the 100G cache and start bumping blocks out.

> If the general guideline is to keep the table size under a couple of million rows

There are too many variables to make a guideline like that.

> then we may have to consider your approach of creating hourly partitions

The one case where this might be useful: If your query is restricted to an hour and you need to scan the entire hour and you don't need all the rows and there is no useful index. Neither of your queries exhibit that scenario.