[4.0][Bug] Paginating enormous database tables

[tabacitu]

Bug report

What I did

Created a CRUD for a table that has 91 million entries.

What I expected to happen

As expected, the create/update operations work well. I expected the List operation to work well too, expecially since I've personally tested it with millions of entries before (1-3 mil, never as high as 91).

What happened

ListOperation doesn't work. The page loads, but the AJAX request takes too much time, so after 1-2 minutes you're presented with a red error bubble saying something went wrong with the datas set.

What I've already tried to fix it

I've drilled down to find the bottleneck. And it's count(). In ListOperation:70 we have:

    /**
     * The search function that is called by the data table.
     *
     * @return array JSON Array of cells in HTML form.
     */
    public function search()
    {
        $this->crud->hasAccessOrFail('list');

        $totalRows = $this->crud->model->count();
        $filteredRows = $this->crud->count();
        // ...
    }

So we have two places where we do count(), and we do that in order to:

• output "Showing 1 to 25 of 900 entries (filtered from 1,000 total entries)";
• correctly paginate the DataTables;

But. Because it's such a big table, MySQL has a difficult time counting the number of rows. It takes more than 60 seconds to do a count(), rendering that ListOperation basically useless.

I've tried to:

(1) Change the DataTables pagination to "full" in order to NOT show page numbers. Since the counts() are still executed in PHP, this is not an actual solution. The AJAX response loads just as slow.

(2) In addition to setting JS pagination to "full", to give it some ridiculously high count numbers (static), instead of doing the actual database count(). So basically:

    /**
     * The search function that is called by the data table.
     *
     * @return array JSON Array of cells in HTML form.
     */
    public function search()
    {
        $this->crud->hasAccessOrFail('list');

        $totalRows = 999999999999;
        $filteredRows = 999999999999;
        // ...
    }

This works, but:

• if you give it a bogus number that's SMALLER than the actual count, the user won't be able to reach the last page of results;
• if you give it a bogus number that's BIGGER than the actual count, the pagination will still show a NEXT button, even if you're at the end of the results; I guess this is the smaller evil in this case;
• in both cases, the "Showing 1 to 25 of 900 entries (filtered from 1,000 total entries)" is complete BS, so it would probably be better to hide it altogether;
• if count() is so slow, you bet your ass that Searching will also be super slow; so that doesn't work and should be hidden;
• if count() is so slow, clicking a column heading to sort by it is also super slow; so that doesn't work and should be hidden;
• I haven't tested this yet, but if count() is so slow, I expect filtering to be just as slow; I don't expect to be able to add filters to db tables that are so huge;

(3) You could theoretically get the total count from INFORMATION_SCHEMA instead of the actual table - which would not be 100% accurate all the time since it's cached, but it would be very close. I believe this is what DB Management Software like Sequel Pro / Tabler to. This would solve $totalRows, and be reasonably accurate. But it would still break the sorting / search / filtering. So the ListOperation will be a very basic thing.

Backpack, Laravel, PHP, DB version

When I run php artisan backpack:version the output is:

### PHP VERSION:
PHP 7.2.20 (cli) (built: Jul  5 2019 12:51:26) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.2.20, Copyright (c) 1999-2018, by Zend Technologies

### LARAVEL VERSION:
laravel/framework                     v6.9.0   The Laravel Framework.

### BACKPACK VERSION:
backpack/crud                         4.0.30   Quickly build an admin interfaces using Laravel 6, CoreUI, Boostrap 4 and jQuery.
backpack/generators                   2.0.5    Generate files for laravel projects
backpack/logmanager                   3.0.1    An interface to preview, download and delete Laravel log files.
backpack/permissionmanager            5.0.2    Users and permissions management interface for Laravel 5 using Backpack CRUD.
backpack/settings                     3.0.1    Application settings interface for Laravel 5 using Backpack CRUD.

[tabacitu]

Note:

In a way, you'd expect things not to work 100% when the table is so large. Backpack couldn't possibly work fast, if the actual Laravel/MySQL query is slow. But on the other hand... I need to have a listing / filtering solution for a table this big. And I expect some people would too (though not a lot of people).

I think it's a cool problem to solve. It won't impact 99.9% of the use cases, but since I need it for myself, I'm eager to see if we can find a decent solution for it. Posting here so I don't forget what I've already tried, and to see if anybody else has some bright ideas.

[ziming]

Why not provide different pagination style options?

1. Previous 15 / Next 15 (Use when your table is big). Doesn't include count of the table or at most an estimate.
2. 1,2,3,4,5,...,10, 11, 12 (Default choice for most people). Also include the count of the table.

For the search, it may not be very slow if you can send it to the right tool -> laravel scout -> ???

[pxpm]

Hey @ziming

Thanks for input.

Trully that was the only solution we have atm that we think will work best in this scenarios.

Best,
Pedro

[tabacitu]

Hi @ziming & @pxpm ,

Indeed that's the solution I thought would be simple to implement. But it's not that simple after all.

Maybe I wasn't clear in my first post: even if you're not actually showing the total number anywhere inside the page, DataTables still requires you to send the total count on each AJAX request. It's a requirement if you're loading the entries using AJAX (which we are). Even if the pagination is just "Previous Next", DataTables uses the total count to figure out when the "Next" button should be disabled...

So even if we do implement simple pagination for HUGE tables, we'll also need to do some dirty stuff to trick DataTables:

• hide the "showing 1 to 10 of 99999999999 entries" text since the total won't be accurate;
• basically hardcode a huge total (9999999999999);
• and still, on the last page, the "Next" button will still be active instead of inactive; though I don't expect many people to reach the last page for such tables so I don't know if it's an actual problem to solve;

Soo... it's a solution... but it's not exactly a pretty one :-) Feels more like a hack.

[ankurkwv]

In ListOperation where we call count() on the model, we could first check for a model function to implement total model count via some other means such as a counter table (of course they'd have to implement in application somehow).

Though, I'm not sure this can be applied to the filtered rows query count.

[tabacitu]

Thanks for pitching in @ankurkwv .

Indeed we could do that in the Model but:

• as you said, we still won't be able to get the filtered count
• it would be VERY dbms-specific; it would need custom code for MySQL, custom code for PosgreSQL, etc;

Sad but true 😞

[tabacitu]

Fixed a long time ago, in v6 - Backpack supports millions of records now - https://backpackforlaravel.com/docs/6.x/crud-operation-list-entries#large-tables-millions-of-entries

So let's close this discussion.