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.