Details

    • Type: Problem report
    • Status: Closed
    • Priority: Trivial
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.0.0rc1, 4.0 (plan)
    • Component/s: Server (S)
    • Labels:
      None
    • Team:
      Team A
    • Sprint:
      Sprint 42, Sprint 43
    • Story Points:
      1

      Description

      When server is reading escalations from database (for example to send notifications) it does not use index for searching and for sorting.

      explain select escalationid,actionid,triggerid,eventid,r_eventid,nextcheck,esc_step,status,itemid,acknowledgeid from escalations where triggerid is not null and mod(triggerid,2)=1 order by actionid,triggerid,itemid,escalationid;
      +------+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+
      | id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
      +------+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+
      |    1 | SIMPLE      | escalations | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where; Using filesort |
      +------+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+
      
      explain select escalationid,actionid,triggerid,eventid,r_eventid,nextcheck,esc_step,status,itemid,acknowledgeid from escalations where triggerid is null and itemid is null and mod(escalationid,2)=1 order by actionid,triggerid,itemid,escalationid;
      +------+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+
      | id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
      +------+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+
      |    1 | SIMPLE      | escalations | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where; Using filesort |
      +------+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+
      
      show create table escalations;
      +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
      +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | escalations | CREATE TABLE `escalations` (
        `escalationid` bigint(20) unsigned NOT NULL,
        `actionid` bigint(20) unsigned NOT NULL,
        `triggerid` bigint(20) unsigned DEFAULT NULL,
        `eventid` bigint(20) unsigned DEFAULT NULL,
        `r_eventid` bigint(20) unsigned DEFAULT NULL,
        `nextcheck` int(11) NOT NULL DEFAULT 0,
        `esc_step` int(11) NOT NULL DEFAULT 0,
        `status` int(11) NOT NULL DEFAULT 0,
        `itemid` bigint(20) unsigned DEFAULT NULL,
        `acknowledgeid` bigint(20) unsigned DEFAULT NULL,
        PRIMARY KEY (`escalationid`),
        UNIQUE KEY `escalations_1` (`actionid`,`triggerid`,`itemid`,`escalationid`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
      +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      
      

        Attachments

          Activity

            People

            • Assignee:
              vso Vladislavs Sokurenko
              Reporter:
              vso Vladislavs Sokurenko
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: