Count Large table

Grocery CRUD Enterprise
1

I have a very large data table. How can I stop it from doing the total count?

pe Showing 1 to 25 of 63,087 records (filtering from 671,531,669 total records). This wastes a lot of time. Thanks.

2

Hello @Saturnino ,

The total count is in order to calculate the paging. If that takes a lot of time then you can hardcode the value with a custom model like this one:

<?php

use GroceryCrud\Core\Model;

class customModel extends Model {

    public function getTotalItems()
    {
        // If there is a filter then we need to calculate the total items
        // so we can have a proper paging
        if (!empty($this->_filters) || !empty($this->_filters_or)) {
            return parent::getTotalItems();
        }

        // Hardcoded or you can even use a caching layer at your system
        return 1000000;
    }
}

You can check how to add a custom model from the documentation here: setModel - Grocery CRUD - A PHP CRUD Generator Library

Please keep in mind that the query to get the number is the COUNT(*) which usually doesn’t take much time to take all the rows (e.g. SELECT COUNT(*) FROM customers). For example I’ve tried to do the query (mySQL) for 1 million rows and it took 92 microseconds (0.000092 seconds) so I guess the filtering is taking too much time. If this is the case then you can do something like this:

<?php

use GroceryCrud\Core\Model;

class customModel extends Model {

    public function getTotalItems()
    {
        if (!empty($this->_filters) || !empty($this->_filters_or)) {
            return 1000;
        }

        return parent::getTotalItems();
    }
}

The above code will always return the filtering to have 1000 rows but in case it hasn’t the paging will just show empty pages.

Regards
Johnny

3

My postgresql table has 600 millions (at the future 2000 M) rows y the rdbms spends 67 seconds on the count(*). It doesn`t read the datum from db dictionary as mysql. Thank (gracias) for the solution.
BR (Saludos)

1 Like
4

It is No solution, I need avoid de count() from all table ( from 671,531,669 total records) no from filtered rows. The count() is from all table always , I have put a $crud->where([‘transitos.matricula like ?’=>‘8068%’]); and it shows the total from de table.

5

Hello @Saturnino ,

Then you can always just return a hardcoded value like this:

<?php

use GroceryCrud\Core\Model;

class customModel extends Model {

    public function getTotalItems()
    {
         return 1000;
    }
}
6

Example:

Displaying 1 to 50 of 67.614 items (filtered from 671.531.669 total entries)

I want to avoid the calculate of 671.531.669, it is the “select count(*) from table;” without conditions. When I define de getTotalItems only I change de 67.614, it isn`t the solution.

7

Hello @Saturnino ,

With the example that I gave above:

<?php

use GroceryCrud\Core\Model;

class customModel extends Model {

    public function getTotalItems()
    {
         return 1000;
    }
}

it will not calculate again the total items. If it doesn’t work for you then maybe clearing your website cache would help.