0

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.

  • it is absolutely normal, that ypu have to select your own choices use FORCE INDEX – nbk Jul 2 at 15:25
  • I don't like force index in my code. – Nir Jul 2 at 21:02
  • yes i see that, but if you want to use a specific INDEx which mysql doesn't choose , you have to use FORCE INDEx – nbk Jul 2 at 21:06
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.

| improve this answer | |
  • What is the performance impact of having a generated column and index with generated column? – Nir Jul 3 at 8:03
  • The prize you have to pay is some additional overhead during modification. Space may also inrease, but in your case you will probably save some, since two wide columns in the index are replaced with one narrow column. – Lennart Jul 3 at 8:08
  • This table is wider than what is listed here (as I said in the Q) and contain also BLOBs. I assume every time I update only one of them will get the whole row before the update, correct? – Nir Jul 5 at 6:50
  • I dont see the relevance of your last comment, can you elaborate on how that is imortant in the context of my answer – Lennart Jul 5 at 6:54
  • Let's say I update magic w/o itemId, so in order for it to update retry it will need to get the matching row to get itemId. Now if the row is very wide it will take long (and more IO) to get that row. Let's say I update 100k rows in one update, that's now becomes much heavier update (if I got how generated works). – Nir Jul 5 at 6:58
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.