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