Missing index by name and user_id?
abartov opened this issue · 1 comments
abartov commented
My app has recently slowed to a crawl, and after much rooting around, I have isolated the cause (using MySQL's slow.log) to be a bombardment of queries of the form:
SELECT COUNT(*) FROM `ahoy_events` WHERE `ahoy_events`.`user_id` = 2345438 AND `ahoy_events`.`name` = '[redacted]';
# Time: 2023-08-31T17:07:09.499176Z
# Query_time: 11.285880 Lock_time: 0.000106 Rows_sent: 1 Rows_examined: 250234
This table scan is triggered, for this exact same user_id and event name, every second or two. Where does this query come from? And should it not be covered by an index?
The indices currently on ahoy_events:
mysql> show index from ahoy_events;
+-------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+--------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+--------+---------------+
| ahoy_events | 0 | PRIMARY | 1 | id | A | 3346083 | NULL | NULL | | BTREE | | |
| ahoy_events | 1 | index_ahoy_events_on_visit_id | 1 | visit_id | A | 2218456 | NULL | NULL | YES | BTREE | | |
| ahoy_events | 1 | index_ahoy_events_on_user_id | 1 | user_id | A | 1926168 | NULL | NULL | YES | BTREE | | |
| ahoy_events | 1 | index_ahoy_events_on_name_and_time | 1 | name | A | 12 | NULL | NULL | YES | BTREE | | |
| ahoy_events | 1 | index_ahoy_events_on_name_and_time | 2 | time | A | 2814429 | NULL | NULL | YES | BTREE | | |
+-------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+--------+---------------+
(I started using Ahoy about two years ago, and am now at version 4.2.0.)