Wrong index been peeked

Question

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.

Answer 1

Adding OR clauses makes it more difficult to estimate how well the index will filter. One solution is to add a generated always column that calculates whether the predicates for magic and itemId are satisfied, and index that:

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 ,
  retry tinyint GENERATED ALWAYS AS 
      ( case when  (magic is NULL or magic = '' or magic = 'retry') 
               AND (itemId is NULL or itemId = '' or itemId = 'retry')
             then 1 
             else 0
        end
      ) STORED,  
  PRIMARY KEY (`id`),
  KEY `ix_magic_composite` (retry,`fTS`,`insrBLN`),
  KEY `fTS` (`fTS`)
) ENGINE=InnoDB AUTO_INCREMENT=14391289 DEFAULT CHARSET=latin1

The query can then be changed to:

SELECT t.*
FROM tblExample t
WHERE status = 'okay'
and textCol > ''
and insrBLN = 1
and retry
and fTS > '2020-01-01'
and fTS > '2020-01-01'  -- can be removed I assume
order by fTS
limit 50; 

The correct solution is probably to fix the data model, but that may not be possible.

Answer 2

Refactor didn't work. I decided to move to UNION ALL which gives the same results as use index or force index. I choose that approach since it will not require any code change if the index gets dropped or renamed.

Answer 3

There are multiple things getting in the way of efficiency:

• OR. Even "ref_or_null" is less than optimal. To start with, can you avoid having both '' and NULL for the column? That is, clean up the data and the processing to use either '' or NULL. That way, you won't need to test for both.
• Because of (itemId is NULL or itemId = '' or itemId = 'retry'), I recommend picking NULL, not '', so that "ref_or_null_ can be used.
• As you already discovered, UNION (preferably ALL) is a workaround for OR. Alas, that gets messy with more than one OR.
• There are multiple "ranges" (textcol, fTS, retryDT). Only one can be effectively used. And, all too often, the Optimizer cannot correctly pick which one to focus on.
• The Optimizer prefers to "filter" the data (via WHERE) and not worry about sorting (ORDER BY). But when the WHERE clause gets too complex, the Optimizer may abandon WHERE and simply focus on ORDER BY. Your second query is an example of this.
• Actually that second query can be improved by changing INDEX(fTS) to INDEX(status, insrBLN, fTS). That way, it can do some of the filtering before sorting; then finish filtering as it walks through the rows.
• Then, after picking either '' or NULL, that index can be further changed to INDEX(status, insrBLN, magic, itemId, fTS).
• Note that I have the = tests first in the INDEX, and the "range" and/or ORDER BY column (FTS) last. (The ordering of the = columns does not matter.)
• The existence of a second "range" (textcol > '') test prevents having a single index that handles both the filtering and the sorting. (I don't think there is any workaround.)
• The order of columns in ix_magic_composite is suboptimal for all of the queries you presented. The important thing is that insrBLN (tested with =) needs to be before FTS (a range), not after.
• When using UNION, you will need to devise an optimal index for each component of the UNION.