# Index on eventtime of alarm journal table?

**URL:** <https://forum.inductiveautomation.com/t/index-on-eventtime-of-alarm-journal-table/62015>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [July 18, 2022, 4:58pm UTC](https://forum.inductiveautomation.com/t/index-on-eventtime-of-alarm-journal-table/62015 "2022-07-18T16:58:43Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [July 18, 2022, 4:58pm UTC](https://forum.inductiveautomation.com/t/index-on-eventtime-of-alarm-journal-table/62015/1 "2022-07-18T16:58:43Z")

</div>

We are getting these warnings in our gateway log. I believe this is part of the automatic pruning for our catchall alarm journal.

```auto
Slow query "DELETE FROM alarm_event_data WHERE "id" IN ( SELECT "id" FROM alarm_events WHERE "eventtime"<=?)" took 34 seconds

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/4/44a470f76fdd1db14c2b386ace9818daee7ffa4c.png)

The alarm\_events table has ~11 million rows (1-year alarm retention). Queries executed against different tables in the same database take milliseconds, so I’m inclined to think that problems are related to this table specifically.

I don’t think I can change these queries, so, in an attempt to fix them, I added an index to eventtime on our development gateway/database. This seems to have resolved the slow query errors and hasn’t introduced any noticeable performance impacts.

Some questions before I add the same index to our production database:

1. Can anyone think of a reason I shouldn’t?
2. Are there performance or maintainability concerns I have failed to consider?
3. If this solution has no drawbacks is there a reason why it’s not default behavior when the table is automatically created?

Thanks for your thoughts on the subject.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 18, 2022, 5:18pm UTC](https://forum.inductiveautomation.com/t/index-on-eventtime-of-alarm-journal-table/62015/2 "2022-07-18T17:18:39Z")

</div>

TL/DR: Adding the index is the Right Answer™.

1. Slight increase in INSERT overhead.
2. Tag History queries in general should benefit too, not just pruning.
3. No clue. It should be default behavior.

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [July 18, 2022, 5:56pm UTC](https://forum.inductiveautomation.com/t/index-on-eventtime-of-alarm-journal-table/62015/3 "2022-07-18T17:56:45Z")

</div>

Thanks for the confirmation, @pturmel. I’ll add the index during the next production maintenance window.

---

<div class="post-metadata">

**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\
**Post date:** [January 12, 2023, 8:15pm UTC](https://forum.inductiveautomation.com/t/index-on-eventtime-of-alarm-journal-table/62015/4 "2023-01-12T20:15:08Z")

</div>

Like this for MySQL/MariaDB?

`ALTER TABLE alarm_events ADD INDEX IF NOT EXISTS (eventtime);`

The majority of my slow queries status page is alarm\_event related, so I thought I'd ask the forum and found this thread. 💯

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 12, 2023, 8:36pm UTC](https://forum.inductiveautomation.com/t/index-on-eventtime-of-alarm-journal-table/62015/5 "2023-01-12T20:36:51Z")

</div>

Not sure that's the correct syntax. Doesn't the index need a name?

{ But yes, add an index for eventtime. }

---

<div class="post-metadata">

**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\
**Post date:** [January 12, 2023, 8:43pm UTC](https://forum.inductiveautomation.com/t/index-on-eventtime-of-alarm-journal-table/62015/6 "2023-01-12T20:43:37Z")

</div>

Looks like MariaDB will auto-name it if you don't give an explicit one. I just tried that command on a test server and SHOW CREATE TABLE is now giving me this, which looks about like I'd expect:

```auto
CREATE TABLE `alarm_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eventid` varchar(255) DEFAULT NULL,
  `source` varchar(255) DEFAULT NULL,
  `displaypath` varchar(255) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `eventtype` int(11) DEFAULT NULL,
  `eventflags` int(11) DEFAULT NULL,
  `eventtime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `eventtime` (`eventtime`)
) ENGINE=InnoDB AUTO_INCREMENT=71205 DEFAULT CHARSET=utf8

```