SAMU-98 Presence of a low cost index that does match the order by causes the optimizer to choose a sub-optimal order by index

[midenok]

Links

1. Optimizer Trace Overview

Info

CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` (
  `QUERY` longtext NOT NULL DEFAULT '',
  `TRACE` longtext NOT NULL DEFAULT '',
  `MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT 0,
  `INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Reproduce

1. Load iy_task.sql from SAMU-98

set optimizer_use_condition_selectivity= 1;
set optimizer_trace= 1;
set sn_order_by_limit_optimize_level= 2;

explain select t.sys_id from task t
where t.sys_class_name = 'sn_customerservice_case'
  and t.active = 1
  and t.assignment_group = '58280f58584120006863f2dea01f7f04'
order by t.number
limit  0, 20;

\T /home/midenok/tmp/trace.txt
select trace from information_schema.optimizer_trace \G
\t