Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

Thanks !

Fran├žois

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.