Mysql – How to speed up this simple query

MySQLperformancequery-performance

I am trying to speed a query that grabs data to be used to display a graph for a research project. It's currently VERY slow. It has 18295603 items only and takes about 20s per query. Here is an example query:

SELECT `data_dylos`.`id`, `data_dylos`.`node_id`, `data_dylos`.`dylos_bin_1`,
`data_dylos`.`dylos_bin_2`, `data_dylos`.`dylos_bin_3`,
`data_dylos`.`dylos_bin_4`, `data_dylos`.`big_particles`,
`data_dylos`.`small_particles`, `data_dylos`.`added_on`,
`data_dylos`.`last_modified`, `data_dylos`.`reading_time` FROM `data_dylos`
 WHERE (`data_dylos`.`added_on` > '2014-08-06 23:00:24'  AND
`data_dylos`.`node_id` = 3 ) LIMIT 21

The added_on value is calculated before the call is made.

Here is my show create table data_dylos:

CREATE TABLE `data_dylos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `node_id` int(11) NOT NULL,
  `dylos_bin_1` int(11) DEFAULT NULL,
  `dylos_bin_2` int(11) DEFAULT NULL,
  `dylos_bin_3` int(11) DEFAULT NULL,
  `dylos_bin_4` int(11) DEFAULT NULL,
  `reading_time` datetime DEFAULT NULL,
  `added_on` datetime NOT NULL,
  `last_modified` datetime NOT NULL,
  `big_particles` int(11) DEFAULT NULL,
  `small_particles` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `data_dylos_1eac40ef` (`node_id`),
  KEY `data_dylos_aa73cdcc` (`added_on`)
) ENGINE=InnoDB AUTO_INCREMENT=20029122 DEFAULT CHARSET=latin1

The fields of interest ARE already indexed but it is still very slow.

Any help will be greatly appreciated.

Thanks!

Best Answer

This is as you say a very simple query, so the optimal index here is very clear. Thanks for showing the whole table structure, indexes and full query, not all people do that.

You indexes are in the good direction, but you must know that (in general, there are other query plans like index_merge) a table scan can only use one index. In this case, as you are filtering by two columns, you have to add the multi-column index:

ALTER TABLE data_dylos ADD INDEX (node_id, added_on);

You have to create it on that order (node_id, added_on) and not in reverse (added_on, node_id), as that would be a different index. The difference is that in a BTREE index, after a range join type is used, the rest of the index is useless.

That is the optimal index query-wise. If you continue to have performance problems you may want to review your configuration (specially innodb_buffer_pool_size).

There are additional techniques that may help you, like caching or partitioning, but those are more advanced, or may require more structure changes, so try this first.

Related Question