Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-14696

Inefficient index causing full table scan on problem_tag table

    Details

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

      Description

      | problem_tag | CREATE TABLE `problem_tag` (
        `problemtagid` bigint(20) unsigned NOT NULL,
        `eventid` bigint(20) unsigned NOT NULL,
        `tag` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
        `value` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
        PRIMARY KEY (`problemtagid`),
        KEY `problem_tag_1` (`eventid`),
        KEY `problem_tag_2` (`tag`,`value`),
        CONSTRAINT `c_problem_tag_1` FOREIGN KEY (`eventid`) REFERENCES `problem` (`eventid`) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
      
      explain select eventid,tag,value from problem_tag where eventid in (2,3,6,7,10,12,13,15,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50) order by eventid;
      +------+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+
      | id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
      +------+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+
      |    1 | SIMPLE      | problem_tag | ALL  | problem_tag_1 | NULL | NULL    | NULL |   19 | Using where; Using filesort |
      +------+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+
      

      Suggested:

      CREATE INDEX problem_tag_3 ON problem_tag (eventid,tag,value);
      DROP INDEX problem_tag_1 ON problem_tag;
      DROP INDEX problem_tag_2 ON problem_tag;
      
      explain select eventid,tag,value from problem_tag where eventid in (2,3,6,7,10,12,13,15,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50) order by eventid;
      +------+-------------+-------------+-------+---------------+---------------+---------+------+------+--------------------------+
      | id   | select_type | table       | type  | possible_keys | key           | key_len | ref  | rows | Extra                    |
      +------+-------------+-------------+-------+---------------+---------------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | problem_tag | index | problem_tag_3 | problem_tag_3 | 1542    | NULL |   19 | Using where; Using index |
      +------+-------------+-------------+-------+---------------+---------------+---------+------+------+--------------------------+
      1 row in set (0.00 sec)
      
      

        Attachments

          Activity

            People

            • Assignee:
              wiper Andris Zeila
              Reporter:
              vso Vladislavs Sokurenko
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: