MySQL Forums
Forum List  »  Partitioning

Re: partition by range on decimal type
Posted by: Greg K
Date: November 22, 2012 02:26AM

Hi Rick, thank you for all your hints.

I want to use paritioning to speed up the queries against the table(s) with WHERE clause including the starttime column. The production table(s) are much bigger than this (10x) and query like:

SELECT bts_name, str_to_date(convert(STARTTIME, CHAR(10)), '%Y%m%d%H') as Date, 2G_CSSR_Ph1_den
WHERE STARTTIME>=2012110900 and STARTTIME<=2012111623
AND bts_name IN ('BTEAG0716A', 'BTEAG0716B', 'BTEAG0716C')
ORDER BY bts_name, starttime;

takes long time.

I'm not the architect of this schema, so I cannot modify the table structure much (e.g. cannot add new column 'hr' or shrink), apart from adding/removing indexes or partitioning.
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.
Then I thought than applying a partitioning will make it and be less stressful (however it is probably not concluding from tests) and playing with it as you see.

The dropping of partitions is not the case here, only performance.
Would you recommend partitioning (what?) or maybe a right indexing is enough?

Options: ReplyQuote

Written By
November 20, 2012 07:15AM
November 21, 2012 10:39AM
Re: partition by range on decimal type
November 22, 2012 02:26AM
November 22, 2012 11:02PM
November 23, 2012 12:45AM
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.