Re: Slow query on partitioned tables with over 200M rows
Posted by:
Hong Yang
Date: February 12, 2014 09:59AM
Rick, thank you for your input.
The DB server does have some extra memory to support OS activities. It has a total of 132GB of RAM.
The interface I used to execute the query was MySQL Workbench. Will explore the query option you mentioned.
In this particular query, the other two components of the primary key were filled in, device_key and component_key were both set to specific values. These two columns should narrow down the selection significantly. Would 200M rows still need to be scanned?
Device_key + component_key are more discriminatory, would changing the primary key order help? Make the most discriminatory column as the first component of the PK?
In another case, we need to scan the entire raw data partition within a time range, but also group the qualified rows by device_key and component_key and create summary records, such as SUM, AVG, MIN, and MAX.
Any performance enhancement suggestion? Any concern of using timestamp as part of the PK?
Will look into the possibility of changing datatype to reduce the column size.
Thanks,
Hong