Cancel count(*) query for large table pagination in Nova resource

[phoenixg]

select count(*) as aggregate from `my_very_big_table`

This sql will slow down the database for large table(Like a billion records). How to replace this or cancel this on resource list?

[bonzai]

It's not possible.

[talovicnedim]

We have the same issue since we just tried to create a resource for failed_jobs but seems like we'll need to customize it. What was your solution @phoenixg?

[phoenixg]

@talovicnedim No solution yet. I always hope nova can support API retrieved JSON into a pagination result, not only support model. I can abandon the relationship with models. I just want a pagination view of my result. I can retrieve them from my own. In this way, the big data problem can be avoided.

[falko100]

This is still an issue. Why is this closed?

[crynobone]

Since 3.26.0 you can override the default Query Builder used by Laravel Nova:

class CustomQueryBuilder implements \Laravel\Nova\Query\Builder 
{
    /**
     * Get the count of the total records for the paginator.
     *
     * @return int
     */
    public function getCountForPagination()
    {
        $model = $this->resource::newModel();

        return $this->toBaseQueryBuilder()->getCountForPagination([$model->getKeyName()]);
    }
}

app()->bind('Laravel\Nova\Contracts\QueryBuilder', function ($app, $parameters) {
    return new CustomQueryBuilder(...$parameters);
});

[alnutile]

Since 3.26.0 you can override the default Query Builder used by Laravel Nova:

class CustomQueryBuilder implements \Laravel\Nova\Query\Builder 
{
    /**
     * Get the count of the total records for the paginator.
     *
     * @return int
     */
    public function getCountForPagination()
    {
        $model = $this->resource::newModel();

        return $this->toBaseQueryBuilder()->getCountForPagination([$model->getKeyName()]);
    }
}

app()->bind('Laravel\Nova\Contracts\QueryBuilder', function ($app, $parameters) {
    return new CustomQueryBuilder(...$parameters);
});

use extends not implements otherwise works well though I have NaN in the pagination area that I am looking into now. Thanks @crynobone

[nw-mjl]

Still an issue in Nova 4.

We would need an option that does not do a count(), or uses the table status to get the number of rows. Or pagination that simply has "Prev" and "Next".

[rogerdalmacio]

this is what I did. I created a Builder.php class on App\Nova\Query;

<?php

namespace App\Nova\Query;

class Builder extends \Laravel\Nova\Query\Builder
{
    /**
     * Get the count of the total records for the paginator.
     *
     * @return int|null
     */
    public function getCountForPagination()
    {
        return null;
    }
}

I simply returned null so that the getCountForPagination will not execute.

then on App Service Provider I added this code

app()->bind('Laravel\Nova\Contracts\QueryBuilder', function ($app, $parameters) {
    return new Builder(...$parameters);
});

@crynobone thanks for the idea.

[beezerk23]

Any update on this? Any workaround right now?

[jonnywilliamson]

Coming in here to report the same. Would love if this could be reopened.

Put in a DB:listen closure to catch all long running mysql queries. Tried with pagination on all 3 settings, and then just visiting the index page of a nova resource. The table has 5.2million rows:

[2024-04-15 21:19:11] production.WARNING: Query took 6869.92ms: {"sql":"select count(*) as aggregate from `aims_crew_history`"}
[2024-04-15 21:19:24] production.WARNING: Query took 4287.82ms: {"sql":"select count(*) as aggregate from `aims_crew_history`"}
[2024-04-15 21:19:46] production.WARNING: Query took 4581.54ms: {"sql":"select count(*) as aggregate from `aims_crew_history`"}
[2024-04-15 21:22:32] production.WARNING: Query took 5822.96ms: {"sql":"select count(*) as aggregate from `aims_crew_history`"}
[2024-04-15 21:23:04] production.WARNING: Query took 4359.54ms: {"sql":"select count(*) as aggregate from `aims_crew_history`"}
[2024-04-15 21:25:47] production.WARNING: Query took 6432.54ms: {"sql":"select count(*) as aggregate from `aims_crew_history`"}
[2024-04-15 21:26:07] production.WARNING: Query took 4681.33ms: {"sql":"select count(*) as aggregate from `aims_crew_history`"}

For some resources I'd love a way to not count the rows, it's crippling performance.

[PCodrin]

Since 3.26.0 you can override the default Query Builder used by Laravel Nova:

class CustomQueryBuilder implements \Laravel\Nova\Query\Builder 
{
    /**
     * Get the count of the total records for the paginator.
     *
     * @return int
     */
    public function getCountForPagination()
    {
        $model = $this->resource::newModel();

        return $this->toBaseQueryBuilder()->getCountForPagination([$model->getKeyName()]);
    }
}

app()->bind('Laravel\Nova\Contracts\QueryBuilder', function ($app, $parameters) {
    return new CustomQueryBuilder(...$parameters);
});

For MySQL a faster solution you can extract an estimated count from information_schema:

class CustomQueryBuilder extends Builder
{
    /**
     * Get the count of the total records for the paginator.
     *
     * @return int
     */
    public function getCountForPagination(): int
    {
        $tableName = $this->toBaseQueryBuilder()->from;

        $count = DB::table('INFORMATION_SCHEMA.TABLES')
            ->select('TABLE_ROWS')
            ->where('TABLE_NAME', $tableName)
            ->first();

        return $count?->TABLE_ROWS;
    }
}

[jonnywilliamson]

For MySQL a faster solution is extract table_rows from information_schema:

I wanted to believe this was a silver bullet to this issue, but a cursory check seems like it's just not accurate enough.

https://dba.stackexchange.com/questions/151769/mysql-difference-between-using-count-and-information-schema-tables-for-coun

[jhm-ciberman]

Solution for PostgreSQL

As pointed by @crynobone, you need to override the Nova Query Builder:

<?php

namespace App\Nova\Query;

use Illuminate\Database\Query\Builder as BaseQueryBuilder;
use Laravel\Nova\Contracts\QueryBuilder;
use Laravel\Nova\Query\Builder;

class FastCountQueryBuilder extends Builder implements QueryBuilder
{
    /**
     * The number of records to consider a table as a big table.
     */
    protected static int $bigTableSize = 2_000_000;

    /**
     * Get the count of the total records for the paginator.
     *
     * @return int|null
     */
    #[\Override]
    public function getCountForPagination()
    {
        $query = $this->toBaseQueryBuilder();

        if ($this->driver() === 'pgsql' && $this->isFastCountPossible($query)) {
            $count = $this->getApproximateCount($query);

            if ($count >= static::$bigTableSize) {
                return $count;
            }
        }

        return parent::getCountForPagination();
    }

    /**
     * Checks whether a fast count is possible for the query.
     */
    public function isFastCountPossible(BaseQueryBuilder $query): bool
    {
        return empty($query->wheres)
            && empty($query->joins)
            && empty($query->groups)
            && empty($query->unions);
    }

    /**
     * Get the driver name for the query.
     */
    protected function driver(): string
    {
        /** @disregard P1013 */
        return $this->toBaseQueryBuilder()->getConnection()->getDriverName();
    }

    /**
     * Get a fast approximation of the total records for the paginator using the PostgreSQL statistics.
     */
    protected function getApproximateCount(BaseQueryBuilder $query): int
    {
        return (int) $query->getConnection()
            ->table('pg_stat_user_tables')
            ->where('relname', $query->from)
            ->value('n_live_tup');
    }
}

And then register the binding in your NovaServiceProvider register method:

use App\Nova\Query\FastCountQueryBuilder;

/**
 * Register any application services.
 *
 * @return void
 */
public function register()
{
    $this->app->bind(\Laravel\Nova\Contracts\QueryBuilder::class, function ($app, $parameters) {
        return new FastCountQueryBuilder(...$parameters);
    });
}

This solution was tested in Nova 4.32 in a DB with small tables and with huge tables (40gb, 9.6M rows). For huge tables the performance went from 30-90 seconds to a couple of milliseconds at best.