Bug #97709 MySQL 8 Select Count(*) is very slow
Submitted: 20 Nov 2019 13:03 Modified: 7 Jan 2020 14:37
Reporter: Vivek Texeira Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:8.0.17 OS:Ubuntu (Ubuntu 18.04.1 LTS)
Assigned to: CPU Architecture:Any

[20 Nov 2019 13:03] Vivek Texeira
Description:
I have a table with 10 columns, in which two columns are JSON field and total records in the table is 1 million. When I do select count(*) it takes more than 10 seconds to display the results, same query and same data in MySQL 5.7 gives results in 0.9 seconds.

If I remove two JSON columns I get results within 1.94 Seconds.

How to repeat:
CREATE TABLE `cases` (
  `id` INT unsigned NOT NULL AUTO_INCREMENT,
  `info` JSON NOT NULL,
  `party` JSON NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

select count(*) from cases;
[20 Nov 2019 16:37] MySQL Verification Team
Hi Mr. Texeira,

Thank you for your bug report.

However, this is not a bug.

InnoDB SE is based on MVCC principles, which means that each transaction can see a different number of rows. Hence, since its very beginning, due to this fact, InnoDB SE had to physically count all rows sequentially. When there are no big objects, like JSON or BLOB, then it of course goes faster.

If you wish to get an approximate number of rows instantly, you have to query information_schema.

This is all described in our Reference Manual.

Not a bug.
[21 Nov 2019 8:36] Vivek Texeira
Same data set works faster on MySQL 5.7 InnoDB.
[21 Nov 2019 13:02] MySQL Verification Team
Yes, that is expected behaviour.

Organisation of pages has slightly changed, for the new features, so the scanning is slower. But, then again, data scanning is not supposed to be fast.
[22 Nov 2019 5:07] Vivek Texeira
Could you please give me the documentation URL where this is mentioned. Thank you.
[22 Nov 2019 12:45] Norvald Ryeng
Hi Vivek,

I'm sorry, I think Sinisa's explanation is incorrect. There shouldn't be any significant performance drop from 5.7 to 8.0.

However, we're not able to reproduce your results. We don't see any performance regression at all. The test case you provided doesn't contain any data, so we used arbitrary values. Can you provide us with some test data to help us reproduce the bug?

Also, please specify the exact 5.7 version you're using.

Best regards,

Norvald H. Ryeng
[22 Nov 2019 13:51] Vivek Texeira
Hello Norvald,

Following are my table structure and sample data set.

CREATE TABLE `cases` (
 `id` INT unsigned NOT NULL AUTO_INCREMENT,
  `short_description` varchar(511) NOT NULL,
  `case_details` JSON NOT NULL,
  `party_details` JSON NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT INTO cases (`short_description`,`case_details`,`party_details`) VALUES ('Family case was filed by County Of Los Angeles against John in the jurisdiction of Los Angeles County Superior Courts, Central Civil West Courthouse located in Los Angeles, California.', '{"judge": {"link": "", "name": ""}, "case_type": {"link": "", "name": "Child Support", "group": "Family", "category": "Civil"}, "courthouse": {"link": "", "name": "Central Civil West Courthouse"}, "case_status": {"name": "Other Disposed", "category": "Disposed"}, "jurisdiction": {"link": "", "name": "Los Angeles County Superior Courts"}}', '{"more": {"count": 0}, "parties": [{"Other": {"link": "", "name": "FERNANDEZ ANA JANETTA", "attorneys": [], "representation_type": "Unrepresented"}}, {"Plaintiff": {"link": "", "name": "COUNTY OF LOS ANGELES", "attorneys": [{"link": "", "name": "CHILD SUPPORT SERVICES DEPT.", "analytics_link": ""}], "representation_type": "Attorney Represented"}}, {"Defendant": {"link": "", "name": "RUIZ EDWIN SAMUEL", "attorneys": [], "representation_type": "Unrepresented"}}, {"Undetermined": {"link": "", "name": "FERNANDEZ ANA JANETTA", "attorneys": [], "representation_type": "Unrepresented"}}, {"Petitioner": {"link": "", "name": "COUNTY OF LOS ANGELES", "attorneys": [], "representation_type": "Unrepresented"}}, {"Respondent": {"link": "", "name": "RUIZ EDWIN SAMUEL", "attorneys": [], "representation_type": "Unrepresented"}}]}');

My current MySQL version is 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

MySQL 8 Version is Server version: 8.0.17 MySQL Community Server - GPL

Please add at least 1 Million records to see the difference.
[22 Nov 2019 16:42] Norvald Ryeng
Hi Vivek,

We're still not able to reproduce your results. We tried with both 1M and 4M rows:

5.7.27: 1.5s and 6.2s
8.0.17: 1.4s and 4.6s

You only supplied one row, so we inserted that exact same row 1M and 4M times. No special setup, just a plain server with normal settings.

Do you run with any special config in 5.7.27 or 8.0.17?

Best regards,

Norvald
[23 Nov 2019 7:36] Vivek Texeira
My Instance

Attachment: Screenshot from 2019-11-23 13:04:47.png (image/png, text), 128.18 KiB.

[23 Nov 2019 7:38] Vivek Texeira
Hello Norvald,

I have attached a screen shot, I'm using 2GB Ram with SSD AWS (t3.small) instance type with default MySQL configuration with same data provided to you.
[26 Nov 2019 11:55] Norvald Ryeng
Hi Vivek,

I don't doubt that you experience the slowdown, but without being able to reproduce the issue, there's not much we can do.

You say you're running with 2 GB RAM. That is very low. There may be changes in the buffer sizes that affect your query negatively.

Best regards,

Norvald
[27 Dec 2019 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[15 Jan 2020 16:18] MySQL Verification Team
for what it's worth I was able to verify this,  but re-filed it internally:

Bug 30766089 - 8.0.17: SEVERE REGRESSION IN PERFORMANCE OF SELECT COUNT(*) FROM LARGE TABLE WITH LOB
[15 Jan 2020 17:18] MySQL Verification Team
The problem exists in >=8.0.17 and is even worse when innodb_flush_method=O_DIRECT
[24 Mar 2020 11:19] Adrian Jäkel
We recently switched our amazon db instance to 8.0.17 since it's available there now since march 2020 [1] and are experiencing a heavy drop in performance in our counting scripts. Are there any news on the mentioned internal bug?

[1] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/WhatsNew.html
[24 Mar 2020 12:29] MySQL Verification Team
Yes, that bug #30766089 I filed is fixed in 8.0.20.  Please wait for the release.
[21 May 2020 13:30] Peter Frederiksen
From MySQL 8.0 Release Notes -> Changes in MySQL 8.0.20 (2020-04-27, General Availability)

InnoDB: Changes to parallel read threads functionality introduced in MySQL 8.0.17 caused a degradation in SELECT COUNT(*) performance. Pages were read from disk unnecessarily. (Bug #30766089)

I'm running version 8.0.20, still VERY VERY slow !!! Table count 5000, no lobs...
[22 May 2020 12:00] MySQL Verification Team
So, that does not seem to be the same bug, but we still need a fully repeatable test case for 8.0.20 or higher.
[22 May 2020 13:09] Peter Frederiksen
Hi, here goes...

CREATE TABLE `photos` (
  `id` int NOT NULL,
  `albumid` int DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `url` varchar(200) DEFAULT NULL,
  `thumbnailurl` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Attached file, photos.csv, 5000 rows.
Mysql workbench -> select count(*) from <db/schema>.photos;
DB version -> select version() -> 8.0.20
[22 May 2020 13:11] Peter Frederiksen
photos.csv

Attachment: photos.csv (application/vnd.ms-excel, text), 626.47 KiB.

[22 May 2020 13:17] MySQL Verification Team
Hi Mr. Frederiksen,

I hope that you are aware that for :

select count(*) from talbe1;

Innodb has to read all the rows in their entire sizes.

This is described in our Reference Manual.
[22 May 2020 13:27] Peter Frederiksen
Hi
yes, i know... (or i'm missing...?)
DB ver. 5.7.30 -> Select count(*).... -> Duration = 0 sec
DB ver. 8.0.20 -> Select count(*).... -> Duration = never under a 1 sec !!!
[22 May 2020 14:08] MySQL Verification Team
This report remains in the status "Can't repeat".

My results with 8.0.20:

+----------+
| count(*) |
+----------+
|     5000 |
+----------+

real	0m0.058s
user	0m0.010s
sys	0m0.008s

+----------+
| count(*) |
+----------+
|     5000 |
+----------+

real	0m0.018s
user	0m0.009s
sys	0m0.005s

+----------+
| count(*) |
+----------+
|     5000 |
+----------+

real	0m0.065s
user	0m0.009s
sys	0m0.005s

and these are my results with 5.7.30:

+----------+
| count(*) |
+----------+
|     5000 |
+----------+

real	0m0.029s
user	0m0.007s
sys	0m0.004s

+----------+
| count(*) |
+----------+
|     5000 |
+----------+

real	0m0.031s
user	0m0.007s
sys	0m0.005s

+----------+
| count(*) |
+----------+
|     5000 |
+----------+

real	0m0.032s
user	0m0.007s
sys	0m0.005s

Can't repeat.
[22 May 2020 14:49] MySQL Verification Team
I've filed the FR for small tables the other day.

Enh 31376535 - IMPROVE PERFORMANCE OF FREQUENT SELECT COUNT(*) FROM SMALL INNODB TABLE 

Best they can do is set innodb_parallel_read_threads=1 for the session.
[23 May 2020 14:01] Peter Frederiksen
Hi.
Sorry, you can't repeat... 
Searched (google) and found 2 of the most common solutions... (pattern)

1. select count(id) from photos -> Duration = never under a 1 sec. (slow)   
2. select count(*) from photos where id > 0  ->  Duration = 0 sec.  (bingo) 

If it's a bug ?
[5 Aug 2020 16:29] Christopher Martin
I'd like to confirm that I have the same problem with MySQL 8.0.21 on my Windows developer machine (64 GB RAM, Ryzen 3900, NVE SSD) where a count of a simple table with ~ 70.000 data sets takes 22 seconds. Adding WHERE id > 0 as suggested above reduces the query time to 0.2 seconds. So there is definitely a bug in MySQL 8. Testing the same table on a much slower windows machine (Surface Pro 3) with Maria 10 DB or any online Host with MySQL 5.7 also gives instant results.
[5 Aug 2020 17:08] Christopher Martin
I just made a few tests. My table has the following fields:

id (INT primary)
year_id (INT index)
text (TEXT)
hash (CHAR(32) unique)

COUNT(*) takes 22s
COUNT(id) takes 22s
COUNT(id) ... WHERE id > 0 takes 0.2s
COUNT(text) takes 0.2s (despite having no index!)
COUNT(hash) takes 0.02s

EXPLAIN SELECT COUNT(id) shows nothing as possible_keys and "year_id" as key (why?)
whereas
EXPLAIN SELECT COUNT(id) ... WHERE id > 0 shows PRIMARY, year_id as possible_key and PRIMARY as key

To me it looks like MySQL 8 "forgets" to use the primary key if COUNT() is unspecific or using the field of the primary key and the query has no WHERE clause
[6 Aug 2020 12:22] MySQL Verification Team
Hi,

I was not able to repeat last example as well.

However, this is not important, since an internal feature request is already filed for the situations as described in this report.
[11 Dec 2020 7:01] gaijun gao
I ran into the same problem,my MySQL version is 8.0.22.I used the script to insert 199w data, count(*) took 40s but where id>0 took 1.12s.Version 5.7 takes 0.8s
[11 Dec 2020 13:30] MySQL Verification Team
Hi ,

These are expected results.

COUNT(*) can't use indices, due to MVCC, while filtering condition can. Even if filtering data can not use any index, it could benefit from the already cached data pages.
[4 Mar 2021 9:32] kenneth Borg
Hi everyone,

We have encountered the same issue, after some extensive test we found that setting the variable innodb_flush_method to FSYNC. The performance improves dramatically.
[9 Apr 2021 9:24] Nilay Shah
We see same issue on mysql 8.0.11..on prod it takes 30 mins 

prod: db.m5.large

mysql> select * from schema_migrations order by version desc limit 1;
+----------------+
| version        |
+----------------+
| 20150825155622 |
+----------------+
1 row in set (0.14 sec)

mysql> select count(*) from tins;
+----------+
| count(*) |
+----------+
|  5806393 |
+----------+
1 row in set (31 min 40.19 sec)

mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
|   3307048 |
+-----------+
1 row in set (0.66 sec)

again repeating count:

mysql> select count(*) from tins;
+----------+
| count(*) |
+----------+
|  5806393 |
+----------+
1 row in set (32 min 22.14 sec)

mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
|   3307048 |
+-----------+
1 row in set (0.66 sec)

qa: db.m5.large

mysql> select * from schema_migrations order by version desc limit 1;
+----------------+
| version        |
+----------------+
| 20200729182351 |
+----------------+
1 row in set (0.10 sec)

mysql> select count(id) from tins;
+-----------+
| count(id) |
+-----------+
|   5105547 |
+-----------+
1 row in set (30.12 sec)

mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
|   3235004 |
+-----------+
1 row in set (0.52 sec)

again repeating count:

mysql> select count(id) from tins;
+-----------+
| count(id) |
+-----------+
|   5105547 |
+-----------+
1 row in set (1.55 sec)

mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
|   3235004 |
+-----------+
1 row in set (0.52 sec)

dev: db.t3.medium

mysql> select * from schema_migrations order by version desc limit 1;
+----------------+
| version        |
+----------------+
| 20200729182352 |
+----------------+
1 row in set (0.10 sec)

mysql> select count(id) from tins;
+-----------+
| count(id) |
+-----------+
|   5699381 |
+-----------+
1 row in set (1 min 30.39 sec)

mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
|   3234976 |
+-----------+
1 row in set (11.31 sec)

again repeating count:

mysql> select count(id) from tins;
+-----------+
| count(id) |
+-----------+
|   5699381 |
+-----------+
1 row in set (1 min 42.00 sec)

mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
|   3234976 |
+-----------+
1 row in set (7.76 sec)
[9 Apr 2021 9:49] Nilay Shah
Also few parameter changes suggested by members are not possible on AWS RDS  since they are not modifiable:

innodb_flush_method to FSYNC

innodb_parallel_read_threads=1
[9 Apr 2021 10:12] Nilay Shah
TINS table structure:

| tins  | CREATE TABLE `tins` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `cartridge_id` int(11) DEFAULT NULL,
  `tin` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `sourcedid` varchar(255) DEFAULT NULL,
  `score` text,
  `outcome_service` varchar(255) DEFAULT NULL,
  `used` int(11) DEFAULT NULL,
  `short_tin` varchar(255) DEFAULT NULL,
  `customer` varchar(255) DEFAULT NULL,
  `entitlement` varchar(255) DEFAULT NULL,
  `access_token` varchar(255) DEFAULT NULL,
  `tin2` varchar(255) DEFAULT NULL,
  `bundle_confirmation` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6213262 DEFAULT CHARSET=utf8 |
[9 Apr 2021 11:03] Nilay Shah
just a clarification,the environments where the select is faster the data type of the column "id" is bigint whereas in prod it is int.
[9 Apr 2021 11:13] Nilay Shah
when where clause is passed as expected it will go for fetching via index but not same thing seen while doing a simple "SELCT COUNT(ID) FROM TINS;" inspite of a primary key on "id" column

mysql> explain  select count(id) from tins WHERE ID > 0;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | tins  | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 2824006 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.31 sec)

mysql> explain  select count(id) from tins;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.31 sec)
[26 Apr 2021 15:30] Alexey Korepov
I still have the same problem with COUNT(*) queries on tables, containing JSON fields,
Package: mysql-server                    
Version: 8.0.23-0ubuntu0.20.04.1

Here is example with 2 similar tables, have 6.5 million of rows, but second one have one JSONB field:
```
mysql> select count(*) from items;
+----------+
| count(*) |
+----------+
|  6354445 |
+----------+
1 row in set (8.02 sec)

mysql> select count(*) from items_with_json_field;
+----------+
| count(*) |
+----------+
|  6433889 |
+----------+
1 row in set (5 min 55.18 sec)
```

So, it is still not fixed in 8.0.20 version??
[27 Apr 2021 12:20] MySQL Verification Team
Hi Mr. Korepov,

This report can not be fixed, since we were not provided with a test case that would enable us to see any slowdown.

So far, all test cases got the same results on both 5.7 and 8.0,
[28 Jul 2021 8:20] Luciano lucianocn
I am also having the same experience on version 8.0.25 (AWS RDS).

Count(*) in a table (800k records) with JSON column takes more than 50 seconds, removing it returns in ~350ms.
[11 Aug 2021 19:05] Jesse Redl
I recently encountered this as well. I have a table that several JSON fields. This table has roughly 45 million rows within it. 

CREATE TABLE `loop` (
  `post_id` varchar(255) NOT NULL,
  `type_id` varchar(125) NOT NULL,
  `partner_id` varchar(10) DEFAULT NULL,
  `market_id` varchar(100) DEFAULT NULL,
  `user_id` varchar(100) DEFAULT NULL,
  `resources` json DEFAULT NULL,
  `template_id` varchar(255) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  `deleted` datetime DEFAULT NULL,
  `likes` json DEFAULT NULL,
  `subscribers` json DEFAULT NULL,
  `last_event_date` datetime DEFAULT NULL,
  `icon` json DEFAULT NULL,
  `_version` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`type_id`,`post_id`),
  KEY `idx_loop_prod_timestamps` (`partner_id`,`type_id`,`created`,`deleted`,`updated`),
  KEY `idx_loop_prod` (`created` DESC,`partner_id`,`type_id`,`market_id`,`user_id`,`deleted`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

A query that looks as follows returns in less 80ms:

SELECT 
    type_id, post_id
FROM
    the_loop.loop 
WHERE
    (partner_id = 'VMF'
        AND type_id IN ('PostType-275e2809-5658-48ba-b3cf-0884f5851347' , 'PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba',
        'PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301',
        'PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9',
        'PostType-1cdac073-9cfd-4388-98ca-86b140921adb',
        'PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')
        AND created BETWEEN '2021-02-09 00:00:00'
        AND created < '2021-08-08 23:59:59.999'
        AND deleted IS NULL)
ORDER BY created DESC
LIMIT 25 OFFSET 0;

A count of this dataset takes over a minute to return:

SELECT 
    count(*)
FROM
    the_loop.p_the_l_loopp_2c4ca9ca_272f_42ef_9d30_43bb11463bd0_mysql8 
WHERE
    (partner_id = 'VMF'
        AND type_id IN ('PostType-275e2809-5658-48ba-b3cf-0884f5851347' , 'PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba',
        'PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301',
        'PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9',
        'PostType-1cdac073-9cfd-4388-98ca-86b140921adb',
        'PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')
        AND created BETWEEN '2021-02-09 00:00:00'
        AND created < '2021-08-08 23:59:59.999'
        AND deleted IS NULL)

On mysql 5.7 the query count take approx 3 seconds. What other information can we provide to prioritize this issue?
[11 Aug 2021 19:06] Jesse Redl
I recently encountered this as well. I have a table that several JSON fields. This table has roughly 45 million rows within it. 

CREATE TABLE `loop` (
  `post_id` varchar(255) NOT NULL,
  `type_id` varchar(125) NOT NULL,
  `partner_id` varchar(10) DEFAULT NULL,
  `market_id` varchar(100) DEFAULT NULL,
  `user_id` varchar(100) DEFAULT NULL,
  `resources` json DEFAULT NULL,
  `template_id` varchar(255) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  `deleted` datetime DEFAULT NULL,
  `likes` json DEFAULT NULL,
  `subscribers` json DEFAULT NULL,
  `last_event_date` datetime DEFAULT NULL,
  `icon` json DEFAULT NULL,
  `_version` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`type_id`,`post_id`),
  KEY `idx_loop_prod_timestamps` (`partner_id`,`type_id`,`created`,`deleted`,`updated`),
  KEY `idx_loop_prod` (`created` DESC,`partner_id`,`type_id`,`market_id`,`user_id`,`deleted`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

A query that looks as follows returns in less 80ms:

SELECT 
    type_id, post_id
FROM
    the_loop.loop 
WHERE
    (partner_id = 'VMF'
        AND type_id IN ('PostType-275e2809-5658-48ba-b3cf-0884f5851347' , 'PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba',
        'PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301',
        'PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9',
        'PostType-1cdac073-9cfd-4388-98ca-86b140921adb',
        'PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')
        AND created BETWEEN '2021-02-09 00:00:00'
        AND created < '2021-08-08 23:59:59.999'
        AND deleted IS NULL)
ORDER BY created DESC
LIMIT 25 OFFSET 0;

A count of this dataset takes over a minute to return:

SELECT 
    count(*)
FROM
    the_loop.p_the_l_loopp_2c4ca9ca_272f_42ef_9d30_43bb11463bd0_mysql8 
WHERE
    (partner_id = 'VMF'
        AND type_id IN ('PostType-275e2809-5658-48ba-b3cf-0884f5851347' , 'PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba',
        'PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301',
        'PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9',
        'PostType-1cdac073-9cfd-4388-98ca-86b140921adb',
        'PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')
        AND created BETWEEN '2021-02-09 00:00:00'
        AND created < '2021-08-08 23:59:59.999'
        AND deleted IS NULL)

On mysql 5.7 the query count take approx 3 seconds. What other information can we provide to prioritize this issue?
[11 Aug 2021 21:28] Rick James
Nilay Shah -- Please find out what ROW_FORMAT was used on each version.
[12 Aug 2021 11:42] MySQL Verification Team
Hi, 

This is actually expected behaviour.

You have not provided an EXPLAIN for the query (queries), but it is most probably that JSON values have to be read from the records itself.

Since, those column types are usually large, that slows down significantly the whole query.
[16 Sep 2021 3:20] Jesse Redl
Explain statement for the query I mentioned above:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "4672714.02"
    },
    "table": {
      "table_name": "loop",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "idx_loop_prod_timestamps",
        "idx_loop_prod",
        "idx_loop_prod_type_id_partner_id_created_deleted"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "type_id"
      ],
      "key_length": "502",
      "rows_examined_per_scan": 23298882,
      "rows_produced_per_join": 7,
      "filtered": "0.00",
      "cost_info": {
        "read_cost": "4672713.29",
        "eval_cost": "0.72",
        "prefix_cost": "4672714.02",
        "data_read_per_join": "24K"
      },
      "used_columns": [
        "type_id",
        "partner_id",
        "created",
        "deleted"
      ],
      "attached_condition": "((`the_loop`.`loop`.`partner_id` = 'VMF') and (`the_loop`.`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`the_loop`.`loop`.`created` between '2021-02-09 00:00:00' and `the_loop`.`loop`.`created`) < '2021-08-08 23:59:59.999') and (`the_loop`.`loop`.`deleted` is null))"
    }
  }
}
[16 Sep 2021 3:22] Jesse Redl
Here is the optimizer trace of the above query.

Attachment: optimzer.txt (text/plain), 19.67 KiB.

[16 Sep 2021 12:15] MySQL Verification Team
Hi,

count(*) queries are slower than queries that return columns in the result sets. A difference is that InnoDB storage engine has, literally, to count number of rows that it traverses. This is because InnoDB SE is a fully MVCC compliant SE. 

We also do not have both 5.7 and 8.0 EXPLAIN's that would show the difference. The example that you have quoted for 8.0 also does not have ORDER with LIMIT. So, that would be helpful.

Next, and very important, 8.0.17 is an ancient release. When doing EXPLAINs for 8.0, please use 8.0.36, instead .....
[4 Oct 2022 4:39] Akira ISIGE
I recently encountered this problem, COUNT(*) on 8.0 is significantly slower than 5.7.

```
DROP SCHEMA IF EXISTS example;
CREATE SCHEMA example;
USE example;

DROP TABLE IF EXISTS table1;
CREATE TABLE table1
(
    id     INT AUTO_INCREMENT,
    col_idx VARCHAR(28),
    col_data  LONGTEXT,
    PRIMARY KEY (id),
    KEY `idx` (`col_idx`)
) CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

DROP PROCEDURE IF EXISTS fill_data;
DELIMITER //

CREATE PROCEDURE fill_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100000
        DO
            INSERT INTO table1 (col_idx, col_data)
            VALUES (REPEAT('a', 28),
                    REPEAT('a', 5000));
            SET i = i + 1;
        END WHILE;
END
//
DELIMITER ;
SET AUTOCOMMIT = true;
call fill_data();
```

An example table has a short VARCHAR column with explicit INDEX and a LONGTEXT column filled large data.

I ran SELECT COUNT(*) in official docker container with docker-compose below.

```
version: '3.8'
services:
  mysql80:
    image: mysql:8.0
    environment:
      MYSQL_DATABASE: example
      MYSQL_ALLOW_EMPTY_PASSWORD: yes
    volumes:
      - ./000_create_table.sql:/docker-entrypoint-initdb.d/000_create_table.sql
  mysql57:
    image: mysql:5.7
    environment:
      MYSQL_DATABASE: example
      MYSQL_ALLOW_EMPTY_PASSWORD: yes
    volumes:
      - ./000_create_table.sql:/docker-entrypoint-initdb.d/000_create_table.sql
  counter:
    image: mysql:8.0-debian
    command: >
      bash -c "
        time mysql -vvv --database example --host mysql80 --execute 'SELECT COUNT(80) FROM table1' ;
        time mysql -vvv --database example --host mysql57 --execute 'SELECT COUNT(57) FROM table1'"
    profiles:
      - count
```

The result of `counter` on my Windows PC shows 8.0 is clearly slower than 5.7.

```
--------------
SELECT COUNT(80) FROM table1
--------------
 
+-----------+
| COUNT(80) |
+-----------+
|    100000 |
+-----------+
1 row in set (3.75 sec)

--------------
SELECT COUNT(57) FROM table1
--------------
 
+-----------+
| COUNT(57) |
+-----------+
|    100000 |
+-----------+
1 row in set (0.02 sec)
```

When dropping index `DROP INDEX idx ON table1` , 8.0 remains the same, and 5.7 is twice as slow as 8.0.

```
--------------
SELECT COUNT(80) FROM table1
--------------
 
+-----------+
| COUNT(80) |
+-----------+
|    100000 |
+-----------+
1 row in set (3.01 sec)
 
--------------
SELECT COUNT(57) FROM table1
--------------
 
+-----------+
| COUNT(57) |
+-----------+
|    100000 |
+-----------+
1 row in set (8.31 sec)
```

Can you reproduce this problem in your environment with the above information?
[4 Oct 2022 12:11] MySQL Verification Team
Hi Mr. Isige,

There are hundreds of reports regarding slower performance of 8.0 versus 5.7 in this forum. They are all, either verified or are duplicates of the verified bugs. This is very well known.

Please, read our earlier reports regarding MVCC and counting within engines of that type. Instead of using count(*), please try using one table within Information schema and you will always get instant response.

It is all documented in our Reference Manual.
[6 Oct 2022 22:01] Akira ISIGE
I understand that 8.0 will run slower due to MVCC.

5.7 (with index) is faster in the same situation, that mean, 5.7 does not regard MVCC?