Skip to main content
Mod Moved Comments To Chat
added 28 characters in body
Source Link

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.

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'
order by fTS
limit 50; 

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

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.

Source Link

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'
order by fTS
limit 50; 

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