diff options
| author | Stelios Fragkakis <52996999+stelfrag@users.noreply.github.com> | 2023-08-30 15:29:21 +0300 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2023-08-30 15:29:21 +0300 |
| commit | e3de1518c69b6e8fe40aa6b023c6932a385d08ed (patch) | |
| tree | 786571f7f9171248c05bd5671a62523549f1af1f /database | |
| parent | dde4d49e01eaee8426bb2fc7028fcb003ffd2a1e (diff) | |
Add index to ACLK table to improve update statements (#15890)
* Add index to improve update statements
* Add index to improve select statements
* Improve update statement
Diffstat (limited to 'database')
| -rw-r--r-- | database/sqlite/sqlite_aclk.c | 10 | ||||
| -rw-r--r-- | database/sqlite/sqlite_aclk.h | 3 | ||||
| -rw-r--r-- | database/sqlite/sqlite_aclk_alert.c | 2 |
3 files changed, 14 insertions, 1 deletions
diff --git a/database/sqlite/sqlite_aclk.c b/database/sqlite/sqlite_aclk.c index fedce50eba..95127cbb55 100644 --- a/database/sqlite/sqlite_aclk.c +++ b/database/sqlite/sqlite_aclk.c @@ -499,6 +499,16 @@ void sql_create_aclk_table(RRDHOST *host __maybe_unused, uuid_t *host_uuid __may rc = db_execute(db_meta, sql); if (unlikely(rc)) error_report("Failed to create ACLK alert table index for host %s", host ? string2str(host->hostname) : host_guid); + + snprintfz(sql, ACLK_SYNC_QUERY_SIZE -1, INDEX_ACLK_ALERT1, uuid_str, uuid_str); + rc = db_execute(db_meta, sql); + if (unlikely(rc)) + error_report("Failed to create ACLK alert table index 1 for host %s", host ? string2str(host->hostname) : host_guid); + + snprintfz(sql, ACLK_SYNC_QUERY_SIZE -1, INDEX_ACLK_ALERT2, uuid_str, uuid_str); + rc = db_execute(db_meta, sql); + if (unlikely(rc)) + error_report("Failed to create ACLK alert table index 2 for host %s", host ? string2str(host->hostname) : host_guid); } if (likely(host) && unlikely(host->aclk_sync_host_config)) return; diff --git a/database/sqlite/sqlite_aclk.h b/database/sqlite/sqlite_aclk.h index 705102d741..850ca434e2 100644 --- a/database/sqlite/sqlite_aclk.h +++ b/database/sqlite/sqlite_aclk.h @@ -46,6 +46,9 @@ static inline int claimed() "unique(alert_unique_id));" #define INDEX_ACLK_ALERT "CREATE INDEX IF NOT EXISTS aclk_alert_index_%s ON aclk_alert_%s (alert_unique_id);" +#define INDEX_ACLK_ALERT1 "CREATE INDEX IF NOT EXISTS aclk_alert_index1_%s ON aclk_alert_%s (filtered_alert_unique_id);" +#define INDEX_ACLK_ALERT2 "CREATE INDEX IF NOT EXISTS aclk_alert_index2_%s ON aclk_alert_%s (date_submitted);" + enum aclk_database_opcode { ACLK_DATABASE_NOOP = 0, diff --git a/database/sqlite/sqlite_aclk_alert.c b/database/sqlite/sqlite_aclk_alert.c index 2a30a32344..9960a1bf79 100644 --- a/database/sqlite/sqlite_aclk_alert.c +++ b/database/sqlite/sqlite_aclk_alert.c @@ -407,7 +407,7 @@ void aclk_push_alert_event(struct aclk_sync_host_config *wc) if (first_sequence_id) { buffer_flush(sql); buffer_sprintf(sql, "UPDATE aclk_alert_%s SET date_submitted=unixepoch() " - "WHERE date_submitted IS NULL AND sequence_id BETWEEN %" PRIu64 " AND %" PRIu64 ";", + "WHERE +date_submitted IS NULL AND sequence_id BETWEEN %" PRIu64 " AND %" PRIu64 ";", wc->uuid_str, first_sequence_id, last_sequence_id); if (unlikely(db_execute(db_meta, buffer_tostring(sql)))) |