1

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!

1 Answer 1

3

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.

1
  • 1
    and you should have ORDER BY in all queries with LIMIT - unless you really don't care about which rows will be in the result. Commented Oct 7, 2014 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.