MySQL Forums
Forum List  »  Partitioning

How to partition for performance
Posted by: Doug Wolfgram
Date: July 29, 2015 09:42PM

I have a table that looks like this:

`row_id` int(11) NOT NULL AUTO_INCREMENT,
`filename` varchar(50) DEFAULT NULL,
`timestamp` datetime DEFAULT NULL,
`unit_num` int(3) DEFAULT NULL,
`string` int(3) DEFAULT NULL,
`voltage` float(6,4) DEFAULT NULL,
`impedance` float(6,4) DEFAULT NULL,
`impedance_avg10` float(6,4) DEFAULT NULL,
`amb` float(6,2) DEFAULT NULL,
`ripple_v` float(8,6) DEFAULT NULL,
`voltage_avg10` float(6,4) DEFAULT NULL,
`noise` float(8,6) DEFAULT NULL,
`filtered_impedance` float(6,4) DEFAULT NULL,
`amb_adj` float(6,2) DEFAULT NULL,
PRIMARY KEY (`row_id`),
UNIQUE KEY `timestamp` (`timestamp`,`filename`,`string`,`unit_num`),
KEY `index1` (`filename`),
KEY `index2` (`timestamp`),
KEY `index3` (`timestamp`,`filename`,`string`),
KEY `index4` (`filename`,`unit_num`)
) ENGINE=MyISAM AUTO_INCREMENT=690892041 DEFAULT CHARSET=latin1

It has over 600M rows and I need to extract, sometimes, 100,000 rows for a graph. Usually the rows are based on the unique key, which someone before me named 'timestamp. :) Anyway, since this is a time-based table that grows constantly, I thought partitioning it into partition of say, 50M records each might help since 90% of the graphs are drawn form the last 50M rows.

It is difficult to do a timestamp partition or even a hash since the primary key is just the row_id. Can someone suggest a way to make this work Does the rule still apply that the partition keys MUST exist in every unique and primary index?

Thanks!

Options: ReplyQuote


Subject
Views
Written By
Posted
How to partition for performance
1494
July 29, 2015 09:42PM


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.