CODEWITHSUNDEEP
mysql
ThaiKov
ThaiKov

Reputation: 175

Very slow query on mysql table with 35 million rows

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: enter image description here

  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:

enter image description here

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

Upvotes: 5

Views: 2355

Answers (3)

Paranoid Android
Paranoid Android

Reputation: 5037

Look at your WHERE clause:

WHERE a.screen_id=384 AND a.timestamp BETWEEN 1462670000000 AND 1463374800000 GROUP BY a.answer_id

I would create a composite index (screen_id, answer_id, timestamp) and run some tests. You could also try (screen_id, timestamp, answer_id) to see if it performs better.

The BETWEEN clause is known to be slow though, as any range query. So is COUNT on million of rows. I would count once a day and save the result to a 'Stats' table which you can query when you need...obviously if you do not need live data.

Upvotes: 1

Gogo
Gogo

Reputation: 302

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.

Upvotes: 1

Alex
Alex

Reputation: 17289

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

Upvotes: 4

Related Questions