Very slow query on mysql table with 35 million rows

Question

I am trying to figure out why a query is so slow on my MySQL database. I've read various content about MySQL performance, various SO questions, but this stays a riddle for me.

1. I am using MySQL 5.6.23-log - MySQL Community Server (GPL)
2. I have a table with roughly 35 million rows.
3. This table is being inserted to about 5 times / second

4. The table looks like this:

5. I have indexes on all the columns except for answer_text

The query I'm running is:

SELECT answer_id, COUNT(1) 
FROM answers_onsite a 
WHERE a.screen_id=384 
 AND a.timestamp BETWEEN 1462670000000 AND 1463374800000 
GROUP BY a.answer_id

this query takes roughly 20-30 seconds, then gives a result set:

Any insights?

EDIT

as asked, my show create table:

CREATE TABLE 'answers_onsite' (
  'id' bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  'device_id' bigint(20) unsigned NOT NULL,
  'survey_id' bigint(20) unsigned NOT NULL,
  'answer_set_group' varchar(255) NOT NULL,
  'timestamp' bigint(20) unsigned NOT NULL,
  'screen_id' bigint(20) unsigned NOT NULL,
  'answer_id' bigint(20) unsigned NOT NULL DEFAULT '0',
  'answer_text' text,
  PRIMARY KEY ('id'),
  KEY 'device_id' ('device_id'),
  KEY 'survey_id' ('survey_id'),
  KEY 'answer_set_group' ('answer_set_group'),
  KEY 'timestamp' ('timestamp'),
  KEY 'screen_id' ('screen_id'),
  KEY 'answer_id' ('answer_id')
) ENGINE=InnoDB AUTO_INCREMENT=35716605 DEFAULT CHARSET=utf8

Answer 1

ALTER TABLE answers_onsite ADD key complex_index (screen_id,`timestamp`,answer_id);

Answer 2

you can use mysql Partitioning like this :

alter table answers_onsite drop primary key;
alter table answers_onsite add primary key (id, timestamp) partition by HASH(id) partitions 500;

Running the above may take a while depending on the size of your table.