In the QAN database, this index change helps when there are multiple servers

[gordan-bobic]

The query in question is this one:

SELECT
        qcm.query_class_id AS query_class_id,
        COALESCE(SUM(qcm.query_count), 0) AS query_count,
        COALESCE(SUM(qcm.Query_time_sum), 0) AS query_time_sum,
        COALESCE(MIN(qcm.Query_time_min), 0) AS query_time_min,
        COALESCE(SUM(qcm.Query_time_sum)/SUM(qcm.query_count), 0) AS query_time_avg,
        COALESCE(AVG(qcm.Query_time_med), 0) AS query_time_med,
        COALESCE(AVG(qcm.Query_time_p95), 0) AS query_time_p95,
        COALESCE(MAX(qcm.Query_time_max), 0) AS query_time_max,
        qc.checksum AS checksum,
        qc.abstract AS abstract,
        qc.fingerprint AS fingerprint,
        qc.first_seen AS first_seen
    FROM query_class_metrics AS qcm
    JOIN query_classes AS qc ON qcm.query_class_id = qc.query_class_id
    WHERE qcm.instance_id = ? AND (qcm.start_ts >= ? AND qcm.start_ts < ?)

    GROUP BY qcm.query_class_id
    ORDER BY SUM(qcm.Query_time_sum) DESC
    LIMIT ? OFFSET ?

The index improvement is this one:

ALTER TABLE query_class_metrics
ADD INDEX instance_start (instance_id, start_ts)
ALGORITHM=INPLACE, LOCK=NONE;

How do we selectively apply this (using migrations only if it hasn't been done yet?

[oblitorum]

What do you mean by "selectively apply this"? You mean leave an option to decide whether to add this index or not when doing SSM upgrade? like adding an environment variable

[gordan-bobic]

I mean we should only apply it if it doesn't already exist, because on future upgrades it may already exist depending on what version we are upgrading from/to.

[oblitorum]

OK, got it.