Crud: slow count(*) problem with mysql 8

[axelzuzek]

After upgrading mysql from 5.7 to 8.0.20 a count(*) or count(id) of an indexed table with 100.000 rows (without where) takes > 5 seconds. (~0.00 seconds with 5.7)

count(*) is at least used for list pagination, so all list operations in backback take several seconds longer.

There are several threads with different solutions found via google. But most of them would need a change in the way the rows are counted, eg querying information_schema.

Is this a know issue, are there any recommendations or plans to change pagination in future backpack releases?

thx a lot

[tabacitu]

Ugh. Sorry to hear that's what MySQL 8 does - thanks for the heads-up @axelzuzek ! It's surprising that the new version is _slower_... Wow.

I've also encountered problem with the same root cause, in pre-8 MySQL, but with huge datasets (millions). Our problem is with DataTables - even if we choose a different pagination (pagingType in their lingo), we'd _still_ need to pass the total count. It wouldn't use it, but it requires it.

So I guess the best we can do is create a new List operation. One that doesn't use DataTables. It was in the cards anyway - but now... it looks like we have no other choice, and we should speed it up...

[tabacitu]

Oh okay, so apparently... we could even create our own DataTables pagination if we want... https://datatables.net/plug-ins/pagination/ Maybe they've fixed the above in the latest version - maybe we can use pagingType: simple now without passing a total count. Or maybe we can create our own pagination type that doesn't require it - this needs more investigation. Will do @axelzuzek , thanks!

[axelzuzek]

Hi, I spend some time last night to dig into this. In the end it was a false alarm, sorry.

The reason for the extremely slow count performance was the lower default innodb_buffer_pool_size of mysql 8. After increasing to 4G count(*) of ~1 million rows took 9 seconds for the first time, and then 0.1 seconds for the further. MySQL 5.7 always gets this count done in 0.3 seconds, also for the first time.

My first google hits yesterday showed that the overall count performance of MySLQ 8 with InnoDB is up to 10.000 times slower than it was for 5.7. So I assumed my 8 seconds per list table view was a result of this and it took some time to check the obvious config parameters. But maybe this will help some other guys falling into the same pit later. (Ubuntu 20.04 has Mysql 8 as default database)

[axelzuzek]

Update: I did some further tests. MySQL 8 with innodb uses full table scans for count() - A big buffer_pool_size helps to improve the performance, but the time for count() increases linear with increasing entry size.
For example InnoDB Table with 5 million entries takes ~ 1sec for a count(*) on a high end desktop system. So it will not scale for big data applications.

MySQL suggests to trigger crud-events and have a seperate stats table with entry counters, or to use MyISAM as a non transactional fallback.