[MDEV-14468] Query Optimizer takes the wrong decision Created: 2017-11-22  Updated: 2017-12-27  Resolved: 2017-12-27

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5.56
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Ruben Herold Assignee: Alice Sherepa
Resolution: Not a Bug Votes: 0
Labels: optimizer, order-by-optimization
Environment:

Centos 7
mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

Attachments: File my.cnf.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



 Comments   
Comment by Alice Sherepa [ 2017-11-23 ]

please attach your cnf file(s).

Comment by Ruben Herold [ 2017-11-23 ]

my.cnf is attached

Comment by Alice Sherepa [ 2017-11-23 ]

This is known issue when you combine LIMIT n with ORDER BY.
Optimizer does not have enough info to choose which plan is the best.
As a solution you can force index () to choose a better plan or try to create index on (device_id, seq), which should help. Also there was done massive improvements of ORDER BY Optimization in MariaDB 10.1.1 by Sergey Petrunia https://mariadb.com/kb/en/library/improvements-to-order-by/

Comment by Ruben Herold [ 2017-11-24 ]

> or try to create index on (device_id, seq), which should help.

Now this makes it more worse:

 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 | index | device_id,device_priority,device_program,device_timestamp,device_seq | PRIMARY | 8       | NULL | 2854 | Using where |
 
+------+-------------+--------+-------+----------------------------------------------------------------------+---------+---------+------+------+-------------+
 
1 row in set (0.10 sec)


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,device_seq | PRIMARY | 8       | NULL | 8349 | Using where |
 
+------+-------------+--------+-------+----------------------------------------------------------------------+---------+---------+------+------+-------------+
 
1 row in set (0.00 sec)

now both use primary...

Generated at Thu Feb 08 08:13:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.