Xrhrft

Important Update

Spent 15 hours on this problem, rebuilt and changed indexes and the table many times.

Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue.

The "query cost" increases by about 10,000 (which is a LOT) every hour of using the index.

When rebuilding the index (drop, create) the query cost is back 1.1.

So based on the analyzer performance gets 50,000 times slower in half a day;)

This happens because of INSERTS and UPDATES on the table, it seems the index is increasingly corrupted somehow, about half of the inserted rows seem not to update inside the index.

Simplified table:

    CREATE TABLE `users`

(

  `id`                    int(11)  NOT NULL AUTO_INCREMENT,

  `first_name`            varchar(64) COLLATE utf8_bin DEFAULT NULL,

  `last_name`             varchar(64) COLLATE utf8_bin DEFAULT NULL,

  `d_table_id`            int(11)                      DEFAULT NULL,

  `last_code`             smallint(6)                  DEFAULT NULL,

  `date_created`          datetime NOT NULL,

  `date_assigned`         datetime                     DEFAULT NULL,

  `date_finished`         datetime                     DEFAULT NULL,

  `virt_ready_for_assign` tinyint(1) GENERATED ALWAYS AS ((isnull(`date_assigned`) and isnull(`date_finished`) and (`d_table_id` > 0))) VIRTUAL,

  PRIMARY KEY (`id`),

  KEY `date_assigned` (`date_assigned`),

  KEY `d_table_id` (`d_table_id`),

  KEY `d_table_id_date_assigned` (`d_table_id`, `date_assigned`),

  KEY `date_assigned_date_finished` (`date_assigned`, `date_finished`),

  KEY `virt_ready_for_assign` (`virt_ready_for_assign` DESC)

) ENGINE = InnoDB

  AUTO_INCREMENT = 76124573

  DEFAULT CHARSET = utf8

  COLLATE = utf8_bin

EXPLAIN:

"explain select count(*) from users where virt_ready_for_assign=1"



id  select_type  table              partitions  type  possible_keys          key                    key_len  ref    rows    filtered  Extra

1   SIMPLE       users  NULL        ref   virt_ready_for_assign  virt_ready_for_assign  2        const  696608  100.00    Using index

The problem: The table has 76 million entries and 20 (!!!) of them have virt_ready_for_assign = 1, 99% have virt_ready_for_assign = 0

Why does it show 696608 ?

This query is being executed a lot and sometimes stalls for seconds, but it should be lightning fast..

The reason for the virtual column was to make this query as fast as possible to dedicate a perfect index to it without wasting disk space (or having to rebuild).

It works perfectly after creating the index and after a day with some million of inserts it gets bad and shows a wrong number in explain.

The actual query used is one of those:

"explain select id from users where virt_ready_for_assign=1 FOR UPDATE"

"explain select id from users where virt_ready_for_assign=1 FOR UPDATE SKIP LOCKED"

P.S. I did try run ANALYZE table, it didn't help. Only removing the index and re-creating it will help. But that's not a solution.

Update: JSON explain:

explain format=json select count(*) from users where virt_ready_for_assign=1

  {

  "query_block": {

  "select_id": 1,

  "cost_info": {

  "query_cost": "77786.30"

  },

  "table": {

  "table_name": "users",

  "access_type": "ref",

  "possible_keys": [

  "virt_ready_for_assign"

  ],

  "key": "virt_ready_for_assign",

  "used_key_parts": [

  "virt_ready_for_assign"

  ],

  "key_length": "2",

  "ref": [

  "const"

  ],

  "rows_examined_per_scan": 772200,

  "rows_produced_per_join": 772200,

  "filtered": "100.00",

  "using_index": true,

  "cost_info": {

  "read_cost": "566.30",

  "eval_cost": "77220.00",

  "prefix_cost": "77786.30",

  "data_read_per_join": "1G"

  },

  "used_columns": [

  "d_table_id",

  "date_assigned",

  "date_finished",

  "virt_ready_for_assign"

  ]

  }

  }

  }

The above explain SQL will yield to count(*) = 0

Not one row currently found

Proof of the bug from my point of view:

I created a second identical index and made queries to the table with both indexes, it reproduceable shows a significant time difference in execution.

mysql> select count(*) from users FORCE INDEX  (virt_ready_for_assign_2) where virt_ready_for_assign=1;

+----------+

| count(*) |

+----------+

|      241 |

+----------+

1 row in set (0.02 sec)



mysql> select count(*) from users FORCE INDEX  (virt_ready_for_assign) where virt_ready_for_assign=1;

+----------+

| count(*) |

+----------+

|      300 |

+----------+

1 row in set (0.14 sec)



mysql> select count(*) from users FORCE INDEX  (virt_ready_for_assign_2) where virt_ready_for_assign=1;

+----------+

| count(*) |

+----------+

|      751 |

+----------+

1 row in set (0.01 sec)



mysql> select count(*) from users FORCE INDEX  (virt_ready_for_assign) where virt_ready_for_assign=1;

+----------+

| count(*) |

+----------+

|      745 |

+----------+

1 row in set (0.08 sec)



mysql> select count(*) from users FORCE INDEX  (virt_ready_for_assign_2) where virt_ready_for_assign=1;

+----------+

| count(*) |

+----------+

|       42 |

+----------+

1 row in set (0.01 sec)