0

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)

Update after 30 hours with the "old" index:
Performance degraded by 50 times already, decreasing every second.
A fresh index does it in 0.01 sec.

select count(*) from users force index (virt_ready_for_assign) where virt_ready_for_assign =1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.51 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)

select count(*) from users force index (virt_ready_for_assign) where virt_ready_for_assign =1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.21 sec)
  • Submit a bug at bugs.mysql.com -- They would be interested to hear that indexed Virtual columns have a problem like this. – Rick James Jan 26 at 18:15
  • @RickJames Did so yesterday and updated it with the new findings: bugs.mysql.com/bug.php?id=94068 Sadly it's not just virtual columns, it's also with stored and normal columns. I am starting my last test on this matter by now replacing the trigger I am using on the tinyint(1) with program logic. Triggers are really old mechanic of mysql but it's the last thing "uncommon" on the table. – John Jan 26 at 18:35
  • Which specific version of 8.0? Seems like there was a nasty bug in 8.0.13. – Rick James Jan 29 at 6:27
  • I don't see virt_ready_for_assign_2 in the table definition?? – Rick James Jan 29 at 6:29
  • Perhaps the issue is DESC on the INDEX definition; that is new to 8.0. – Rick James Jan 29 at 6:29

Your Answer

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

Browse other questions tagged or ask your own question.