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

[Hong Yang]

Thanks again, Rick. Appreciate your insight.

>Do you know how many rows it would be? With an ordinary disk drive, it would probably scan 20K rows per second.
I would expect 360 rows per device and component on a partition.

>What is the cardinality of the combination of device_key and component_key? And do you always look at both of them together? I am fishing for normalizing that pair into some sized INT UNSIGNED, thereby shrinking the table a little more.
There are 50K unique devices and each device can have 0 to a couple of hundred components.

Are you aware of performance benchmark results on table size vs query response time? If the general guideline is to keep the table size under a couple of million rows, then we may have to consider your approach of creating hourly partitions for the current day table.

Thanks,
Hong