I have an index that filters 99% of the table i.e. `ix_magic_composite` (for that query arguments).
When I add another `or` filter it chooses the wrong index, i.e. `fTS` even if I create an index that starts with that field it still choose the wrong index. Run times are 20s vs 3s to the better index. `ix_magic_composite` index returns (initial filter) for both SQLs around 10 rows out of millions, while `fTS` returns millions back.

Kind of clueless. It looks to me as the statistics aren't giving the engine the right picture with all those columns combined.

I simplified the table, it has a lot more columns and indexes.


SQL w/ good plan:
    
    select *
    from tblExample
    where 1=1
    and status = 'okay'
    and textCol > ''
    and insrBLN = 1
    and (magic is NULL or magic = '')
    and (itemId is NULL or itemId = '')
    and fTS > '2020-01-01'
    and fTS > '2020-01-01'
    order by fTS
    limit 50
    
    +----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+
    | id | select_type | table      | partitions | type        | possible_keys                                    | key                 | key_len | ref   | rows    | filtered | Extra                                              |
    +----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+
    |  1 | SIMPLE      | tblExample | NULL       | ref_or_null | textCol,status,textCol_4,ix_magic_composite,fTS  | ix_magic_composite  | 53      | const | 5892974 |     0.24 | Using index condition; Using where; Using filesort |
    +----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+
    
SQL w/ bad plan:
    
    select *
    from tblExample
    where 1=1
    and status = 'okay'
    and textCol > ''
    and insrBLN = 1
    and (magic is NULL or magic = '' or magic = 'retry')
    and (itemId is NULL or itemId = '' or itemId = 'retry')
    and fTS > '2020-01-01'
    and fTS > '2020-01-01'
    order by fTS
    limit 50
    
    +----+-------------+------------+------------+-------+-------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+
    | id | select_type | table      | partitions | type  | possible_keys                                   | key     | key_len | ref  | rows    | filtered | Extra                              |
    +----+-------------+------------+------------+-------+-------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+
    |  1 | SIMPLE      | tblExample | NULL       | range | textCol,status,textCol_4,ix_magic_composite,fTS | fTS     | 5       | NULL | 6271587 |    0.18  | Using index condition; Using where |
    +----+-------------+------------+------------+-------+-----------------------------------------    ----+---------+---------+------+---------+----------+------------------------------------+
   
Table:
 
    CREATE TABLE `tblExample` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `fTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      `status` varchar(50) NOT NULL DEFAULT 'new',
      `textCol` varchar(50) DEFAULT NULL,
      `insrBLN` tinyint(4) NOT NULL DEFAULT '0',
      `itemId` varchar(50) DEFAULT NULL ,
      `magic` varchar(50) DEFAULT NULL ,
      PRIMARY KEY (`id`),
      KEY `ix_magic_composite` (`itemId`,`magic`,`fTS`,`insrBLN`),
      KEY `fTS` (`fTS`)
    ) ENGINE=InnoDB AUTO_INCREMENT=14391289 DEFAULT CHARSET=latin1



**EDIT**

We have refactor the code so the query looks like:

    select *
    from tblExample
    where 1=1
    and status = 'okay'
    and textCol > ''
    and insrBLN = 1
    and (retry = '' or (retry='retry' and retryDT < now() - interval 1 day))
    and fTS > '2020-01-01'
    order by fTS
    limit 50

The issue was NOT sorted (also tried different columns order in the index).
It looks like it chooses the right index only if I remove the order by.