[MDEV-9457] Poor query plan chosen for ORDER BY query by a recent 10.1 - Jira

Discovered as part of ~~MDEV-9420~~. See these comments:

Copying here:
Checking problem #1.

MariaDB 10.0:

MariaDB [j10]> explain select * from point_activite

WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: point_activite

         type: index_merge

possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass

          key: id_contact_campaign_pass,id_contact_Script

      key_len: 9,9

          ref: NULL

         rows: 1

        Extra: Using intersect(id_contact_campaign_pass,id_contact_Script); Using where

MariaDB [j10]> explain select * from point_activite

WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183

ORDER BY `IDPoint_Activite`\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: point_activite

         type: index_merge

possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass

          key: id_contact_campaign_pass,id_contact_Script

      key_len: 9,9

          ref: NULL

         rows: 1

        Extra: Using intersect(id_contact_campaign_pass,id_contact_Script); Using where; Using filesort

So, in 10.0, the plan is always to use index_merge to produce one row. Actually, two rows are produced and the query finishes in 0.05 sec or less.

In 10.1.10:

MariaDB [j10]> explain select * from point_activite

WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: point_activite

         type: index_merge

possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass

          key: id_contact_campaign_pass,id_contact_Script

      key_len: 9,9

          ref: NULL

         rows: 1

        Extra: Using intersect(id_contact_campaign_pass,id_contact_Script); Using where

The same so far. Adding ORDER BY:

explain select * from point_activite

WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183

ORDER BY `IDPoint_Activite`\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: point_activite

         type: index

possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass

          key: PRIMARY

      key_len: 8

          ref: NULL

         rows: 1997722

        Extra: Using where

And the plan becomes much worse. It wants to read nearly 2M rows. Execution takes 1 min 39 sec.

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server