[dev.icinga.com #1964] Indices for notification queries

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/1964

Created by mhein on 2011-09-28 09:46:14 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2011-10-22 11:54:21 +00:00)
Target Version: 1.6
Last Update: 2014-12-08 14:37:25 +00:00 (in Redmine)

---

Hi.

We added some information to the notification grid which joins heavily. Some mysql indices helps to speed up the query.

Can you add the index to 1.6 ido2db upgrade, please?

ALTER TABLE icinga_notifications ADD INDEX notification_idx (notification_type, object_id, start_time);
ALTER TABLE icinga_contactnotifications ADD INDEX notification_idx (notification_id, contact_object_id);
ALTER TABLE icinga_contacts ADD INDEX contacts_object_id_idx (contact_object_id);
ALTER TABLE icinga_contactnotificationmethods ADD INDEX notification_idx (contactnotification_id, command_object_id);
ALTER TABLE icinga_commands ADD INDEX command_object_idx (object_id); 
ALTER TABLE icinga_services ADD INDEX services_combined_idx (service_object_id, host_object_id);

Cheers,
Marius.

Changesets

2011-10-22 11:48:36 +00:00 by mfriedrich 2ef1411

* idoutils: add Indices for notification queries for Icinga Web #1964

refs #1964

[icinga-migration]

Updated by mhein on 2011-09-28 09:51:16 +00:00

Its better to have also one single index on this field:

ALTER TABLE icinga_notifications ADD INDEX notification_object_id_idx (object_id);

[icinga-migration]

Updated by mfriedrich on 2011-09-30 21:36:20 +00:00

• Target Version set to 1.6

needs pgsql and oracle too.

[icinga-migration]

Updated by mfriedrich on 2011-10-21 13:00:16 +00:00

• Category set to 24
• Status changed from New to Assigned
• Assigned to set to mfriedrich

[icinga-migration]

Updated by mfriedrich on 2011-10-22 11:44:04 +00:00

oracle is special again and does not allow same index names (which is due to the fact of an own index tablespace and organizing).

[icinga-migration]

Updated by mfriedrich on 2011-10-22 11:54:21 +00:00

• Status changed from Assigned to Resolved
• Done % changed from 0 to 100

up in git test/ido, tested against all 3 rdbms.

[icinga-migration]

Updated by mfriedrich on 2014-12-08 14:37:25 +00:00

• Project changed from 18 to Core, Classic UI, IDOUtils
• Category changed from 24 to IDOUtils