MySQL Forums
Forum List  »  Performance

query performance on partitioning table
Posted by: sixin yang
Date: May 12, 2015 09:00AM

Hi, folks

There's a huge table and it is range-partitioned by a primary key (integer _time_) to 12 parts, one for each month. Each partition will contain about 20 millions rows.

From clean table, when we construct some data into this table for about 3 month/partition (such as 20150101-20150331), we do a SELECT query with WHERE clause: 20150201<_time_<20150202, it will only access the 2nd partition and it results in 3 seconds. However, when we continuously construct data into this table until 12 month/partition (such as 20150101-20151231), we do the same SELECT query, it takes about 50 seconds!

I use the EXPLAIN PARTITIONS to check this query and got that it is only check the 2nd partition(stands for 20150201-20150228), and the TYPE is "range", EXTRA is "Using Where". So i think for the two case, it will be almost the same query time because we use the same query on the same partition. The only difference is the total rows for the table!

Is there some limitation on table partitioning? Or the SELECT query on the primary index will use something like GLOBAL index on the whole table instead of the partition?

I'm using MySQL 5.6.22. and the table is innoDB engine.

Any thoughts would be appreciated.
thanks!

Options: ReplyQuote


Subject
Views
Written By
Posted
query performance on partitioning table
3276
May 12, 2015 09:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.