-
Incident report
-
Resolution: Fixed
-
Minor
-
1.9.8 (beta)
Hi Guys -
As shown below, I added an index to the "history" table on the itemid, clock, and ns fields. This greatly improved performance of many of the common queries which were previously performing filesorts. One common query went from 200 seconds to <.03 seconds. There is a tradeoff that the index is on almost all of the fields. Perhaps an index-only table would help here (if they exist in MySQL).
CREATE index history_itemid_clock_ns on history (itemid,clock,ns)
mysql> show indexes from history ;
------------------------------------------------------------------------------------------------------------------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
------------------------------------------------------------------------------------------------------------------------+
| history | 1 | history_1 | 1 | itemid | A | 17 | NULL | NULL | BTREE | ||
| history | 1 | history_1 | 2 | clock | A | 117385964 | NULL | NULL | BTREE | ||
| history | 1 | history_itemid_clock_ns | 1 | itemid | A | 17 | NULL | NULL | BTREE | ||
| history | 1 | history_itemid_clock_ns | 2 | clock | A | 117385964 | NULL | NULL | BTREE | ||
| history | 1 | history_itemid_clock_ns | 3 | ns | A | 117385964 | NULL | NULL | BTREE |
------------------------------------------------------------------------------------------------------------------------+
Before Index:
mysql> describe select value from history where itemid=24847 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
---------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------+
| 1 | SIMPLE | history | range | history_1 | history_1 | 12 | NULL | 420374 | Using where; Using filesort |
---------------------------------------------------------------------------------------------+
After Index:
mysql> describe select value from history where itemid=22922 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
---------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | history | range | history_1,history_itemid_clock_ns | history_itemid_clock_ns | 12 | NULL | 371280 | Using where |
---------------------------------------------------------------------------------------------------------------+
And the performance gain:
mysql> select value from history ignore index (history_itemid_clock_ns) where itemid=24847 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
------------
| value |
------------
| 64533.2620 |
------------
1 row in set (3 min 22.60 sec)
mysql> select value from history where itemid=24847 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
------------
| value |
------------
| 64533.2620 |
------------
1 row in set (0.00 sec)