Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5.56
-
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 |