I need your help for a partionning problem. We are using a "log_event" table which log a lot of events sent by our players (subscription, connection, play, share, etc...). We insert 5,000 row / s in this table.
This table is quite big (more than 1 billion lines) and the structure is :
CREATE TABLE IF NOT EXISTS `log_event` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `id_event` smallint(6) NOT NULL, `id_inscri` bigint(20) NOT NULL, `date` int(11) NOT NULL, `data` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `id_event_2` (`id_event`,`data`), KEY `id_inscri` (`id_inscri`), KEY `date` (`date`), KEY `id_event_4` (`id_event`,`date`,`data`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ;
In order to increase performance, we would like to partition this table, but :
we don't have real PRIMARY KEY => we are using an AUTO INCREMENT which is useless in our select query. We never use this column but it's the only way we have found to have a PK.
we would like to partition with the columns 'id_event, date, data' but we don't know how to do that because we have the error "#1503 - A PRIMARY KEY must include all columns in the table's partitioning function"
Do you have any ideas on how to increase performance without cutting the possibility of quick SELECT ?