MySQL Forums
Forum List  »  Partitioning

Re: partition by range on decimal type
Posted by: Rick James
Date: November 22, 2012 11:02PM

> WHERE STARTTIME>=2012110900 and STARTTIME<=2012111623
> AND bts_name IN ('BTEAG0716A', 'BTEAG0716B', 'BTEAG0716C')
1. Change the datatype of STARTTIME to INT UNSIGNED so that you can PARTITION on it.
2. Add PARTITION BY RANGE(STARTTIME) ... with 20-50 partitions.
3. Have an index starting with bts_name.
Then, and only then, will that SELECT run faster. (OK, there may be other ways.)

In fact, a non-partitioned table with
INDEX(bts_name, STARTTIME)
will perform well if you have a new enough version of MySQL. (I don't know when that particular optimization (IN, then 'range') was implemented.)

> I cannot modify the table structure
That is not a viable position to be in!

> One developer tried to add index and it hanged (due to huge table's size) so he had to break the process to release the lock.
Virtually any ALTER on a huge table takes a long time. See http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

Options: ReplyQuote


Subject
Views
Written By
Posted
3912
November 20, 2012 07:15AM
1716
November 21, 2012 10:39AM
1408
November 22, 2012 02:26AM
Re: partition by range on decimal type
1489
November 22, 2012 11:02PM
1597
November 23, 2012 12:45AM
1705
November 23, 2012 09:03PM


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.