I am having trouble deciding what fields of my table to base the partition. Currently there are a total of 8 indexes on the table out of a total of 14 columns. The problem is the indexes are taking up too much space 2 to 1 compared to the data and we need to reduce the size of the DB, and at least maintain current speed. 3 of the indexes have a significantly lower cardinality (in the teens) than the rest (in the millions). There are over 40 million rows in the table.
The index data type looks something like this:
datetime smallint(6) (low cardinality) varchar(20) (low cardinality) int(15) int(15) varchar(100) varchar30 char(1) (low cardinality)
and a few others. All the indexes are non unique and of type BTREE.
I created a test table and removed the indexes with the lowest cardinality but the queries take a bit longer. I'd like suggestions on two aspects:
What indexes should be kept and, which should be removed.
What columns should my partitioning be based on.
The table definition:
Create Table: CREATE TABLE `mytable` ( `date` datetime DEFAULT NULL, `time` smallint(6) DEFAULT NULL, `source` varchar(20) DEFAULT NULL, `b` int(15) DEFAULT NULL, `name` varchar(100) DEFAULT NULL, `ttype` char(1) DEFAULT NULL, `ag` char(1) DEFAULT NULL, `direction` char(1) DEFAULT NULL, `mode` char(1) DEFAULT NULL, `username` varchar(30) DEFAULT NULL, `name` varchar(15) DEFAULT NULL, `authentication` char(1) DEFAULT NULL, `authenticated_id` varchar(75) DEFAULT NULL, `status` char(1) DEFAULT NULL, KEY `date` (`date`), KEY `time` (`time`), KEY `b` (`b`), KEY `name` (`name`), KEY `username` (`username`), KEY `source` (`source`), KEY `b2` (`b`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1