Below table contains billion of rows,
CREATE TABLE `Sample1` (
`c1` bigint(20) NOT NULL AUTO_INCREMENT,
`c2` varchar(45) NOT NULL,
`c3` tinyint(4) DEFAULT NULL,
`c4` tinyint(4) DEFAULT NULL,
`time` bigint(20) DEFAULT NULL,
PRIMARY KEY (`c1`),
KEY `varchar_time_idx` (`c2`,`Time`),
KEY `varchar_c3_time_idx` (`c2`,`c3`,`Time`),
KEY `varchar_c4_time_idx` (`c2`,`c4`,`Time`),
KEY `varchar_c3_c4_time_idx` (`c2`,'c3', `c4`,`Time`),
) ENGINE=InnoDB AUTO_INCREMENT=10093495 DEFAULT CHARSET=utf8
Four multi column index created because having below conditions in where
1) c2 and time
2) c2 and c3 and time
3) c2 and c4 and time
4) c2 and c3 and c4 and time
Cardinality wise c2, c3 and c4 are very low. (ex: Out of one million c2, c3 and c4 have 50 unique column in each)
Column time contains mostly unique fields.
Select, insert and update happened frequently.
Tables has 5 indexing fields(4 multi column). Due to this, 1) Insert and update on index fields become costlier. 2) As the table keep on growing (Nearly one billion rows), Index size also increase more rapidly.
Kindly suggest good approach in mysql to solve this use case.
