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

XMLWordPrintable

    • Icon: Problem report Problem report
    • Resolution: Fixed
    • Icon: Trivial Trivial
    • 4.0.0beta1, 4.0 (plan)
    • 4.0.0alpha9
    • Server (S)
    • None
    • Sprint 40, Sprint 41
    • 1

      | problem_tag | CREATE TABLE `problem_tag` (
        `problemtagid` bigint(20) unsigned NOT NULL,
        `eventid` bigint(20) unsigned NOT NULL,
        `tag` varchar(255) COLLATE utf8mb4_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 CHARACTER SET utf8mb4 COLLATE utf8mb4_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)
      
      

            wiper Andris Zeila
            vso Vladislavs Sokurenko
            Team A
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: