Skip to content

Loading…

Performance issue updating node last reports #277

Open
sodabrew opened this Issue · 0 comments

1 participant

@sodabrew
Owner

This is well and truly bizarre and causes report pruning to take a very long time:

mysql> select * from reports where node_id = 2099 and kind = 'inspect' order by time desc;
Empty set (0.00 sec)

mysql> select * from reports where node_id = 2099 and kind = 'inspect' order by time desc limit 1;
Empty set (3.16 sec)

Ah, it's triggering MySQL to use the wrong index:

mysql> explain select * from reports where node_id = 2099 and kind = 'inspect' order by time desc;
+----+-------------+---------+------+--------------------------+--------------------------+---------+-------+------+-----------------------------+
| id | select_type | table   | type | possible_keys            | key                      | key_len | ref   | rows | Extra                       |
+----+-------------+---------+------+--------------------------+--------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | reports | ref  | index_reports_on_node_id | index_reports_on_node_id | 5       | const | 1682 | Using where; Using filesort |
+----+-------------+---------+------+--------------------------+--------------------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select * from reports where node_id = 2099 and kind = 'inspect' order by time desc limit 1;
+----+-------------+---------+-------+--------------------------+----------------------------------------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys            | key                                          | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+--------------------------+----------------------------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | reports | index | index_reports_on_node_id | index_reports_on_time_and_node_id_and_status | 782     | NULL | 1073 | Using where |
+----+-------------+---------+-------+--------------------------+----------------------------------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

(This is MySQL 5.5.35)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.