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 :

  `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`)

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 !


share|improve this question
add comment

Your Answer


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

Browse other questions tagged or ask your own question.