MySQL Forums
Forum List  »  Partitioning

Is partitioning helpful in this case?
Posted by: Aries Belgium
Date: May 22, 2011 04:18AM


We have a table to store historical statistics for every workstation on our network every 5 seconds. Data is coming in and all but as the table is growing fast I'm concurred about performance. The table is growing with ~70 million records every day. I've set the appropriate indexes on the fields that I use to select data from the table but how larger the table gets how longer it takes to select data from the table. This is quite normal of course but I want to lower the data access time so I was investigating the partition feature of MySQL 5.1 but I stumbled upon a problem already. The script we use to gather the stats saves a unix timestamp to the database instead of a datetime value and what I already learned is that partitioning doesn't work on unix timestamps if you want to partition on time ranges. We are to far now to convert all timestamps to datetime fields as it would break all the scripts that are relying on the statistics data. Now I have a few questions:

1) Is it a good idea to add an additional datetime field to the table and create a trigger to convert the timestamp to a datetime value when the row is added? That way I could use the datetime field for partitioning purposes and still be able to use the timestamp within our applications.
2) If so, would partitioning help lowering data access times?
3) If not, do you have another suggestion?

Best regards.

Options: ReplyQuote

Written By
Is partitioning helpful in this case?
May 22, 2011 04:18AM

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.