Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14468

Query Optimizer takes the wrong decision

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 5.5.56
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Environment:
      Centos 7
      mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

      Description

      hi,

      I have here one query where the optimizer often takes the wrong decision:

      explain SELECT * FROM `syslog`  WHERE 1  AND `device_id` = '51' AND (( (`device_id` != '' AND `device_id` IS NOT NULL)))  ORDER BY `seq` DESC LIMIT 0,20
       
      +------+-------------+--------+-------+-----------------------------------------------------------+---------+---------+------+------+-------------+
      | id   | select_type | table  | type  | possible_keys                                             | key     | key_len | ref  | rows | Extra       |
      +------+-------------+--------+-------+-----------------------------------------------------------+---------+---------+------+------+-------------+
      |    1 | SIMPLE      | syslog | index | device_id,device_priority,device_program,device_timestamp | PRIMARY | 8       | NULL | 8414 | Using where |
      +------+-------------+--------+-------+-----------------------------------------------------------+---------+---------+------+------+-------------+
      1 row in set (0.10 sec)
       
      SELECT * FROM `syslog`  WHERE 1  AND `device_id` = '51' AND (( (`device_id` != '' AND `device_id` IS NOT NULL)))  ORDER BY `seq` DESC LIMIT 0,20;
      20 rows in set (2 min 21.60 sec)
       
       explain SELECT * FROM `syslog`  WHERE 1  AND `device_id` = '52' AND (( (`device_id` != '' AND `device_id` IS NOT NULL)))  ORDER BY `seq` DESC LIMIT 0,20
       
      +------+-------------+--------+------+-----------------------------------------------------------+-----------+---------+-------+--------+-------------+
      | id   | select_type | table  | type | possible_keys                                             | key       | key_len | ref   | rows   | Extra       |
      +------+-------------+--------+------+-----------------------------------------------------------+-----------+---------+-------+--------+-------------+
      |    1 | SIMPLE      | syslog | ref  | device_id,device_priority,device_program,device_timestamp | device_id | 5       | const | 293166 | Using where |
      +------+-------------+--------+------+-----------------------------------------------------------+-----------+---------+-------+--------+-------------+
      1 row in set (0.06 sec)
       
      SELECT * FROM `syslog`  WHERE 1  AND `device_id` = '52' AND (( (`device_id` != '' AND `device_id` IS NOT NULL)))  ORDER BY `seq` DESC LIMIT 0,20;
      20 rows in set (0.03 sec)
      

      Table: syslog

      Create Table: CREATE TABLE `syslog` (
        `device_id` int(11) DEFAULT NULL,
        `host` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Hostname or IP received by syslog server',
        `facility` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
        `priority` tinyint(4) NOT NULL DEFAULT '8',
        `level` tinyint(4) NOT NULL DEFAULT '8',
        `tag` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
        `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `program` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
        `msg` text CHARACTER SET utf8,
        `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`seq`),
        KEY `datetime` (`timestamp`),
        KEY `device_id` (`device_id`),
        KEY `program` (`program`),
        KEY `priority` (`priority`),
        KEY `device_priority` (`device_id`,`priority`),
        KEY `device_program` (`device_id`,`program`),
        KEY `device_timestamp` (`device_id`,`timestamp`)
      ) ENGINE=InnoDB AUTO_INCREMENT=53538532 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
      

        Attachments

          Activity

            People

            Assignee:
            alice Alice Sherepa
            Reporter:
            rherold Ruben Herold
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: