Listing large table is slow
I have a 50m rows table. Viewing the resource list page/table is very slow. It takes at least 2-3 minutes to view the table. I believe there's a "count(*)" query for pagination, and this is causing the slowness. There are faster ways to count the rows. Can't I manually provide the total row count?
19 Replies
I see the following logs in Queries of the debugbar:
select count(*) as aggregate from
my_table 64.45s
select * from my_table order by my_table.id asc limit 10 offset 0 830μs
Waiting a min to get the row count is unacceptable.i think what u wanna use is simple pagination
https://filamentphp.com/docs/3.x/tables/advanced#using-simple-pagination
Also, it seems viewing the record (in modal) also triggers the count(*) again. So I'll have to wait for that too.
https://laravel.com/docs/10.x/pagination#simple-pagination
here is the laravel docs on simple paginate hope that helps 😄
Laravel - The PHP Framework For Web Artisans
Laravel is a PHP web application framework with expressive, elegant syntax. We’ve already laid the foundation — freeing you to create without sweating the small things.
If I have to fallback for such a simple requirement, why use filament at all? I'm currently experimenting with filament, so I'm not that familiar with it, but so far, things don't look good.
First thing first, you have debugbar. Thats a big issue for slowness, so ensure you cache icons and remove view collector from the debugbar.
Next you'll need to ensure you are not rendering more than ideally 50 rows at ones, searching will be done quickly as normal. Ensure you have indexes in place too.
how is this a filament with issue?
go ahead and run
select count(*) as aggregate from my_table in your database client
it will take the same time
Laravel pagination does not work without a query to fetch the total number of rows
hence why you need to use simple paginationThe debugbar was later installed to inspect the issue. Icons are already cached. Table is indexed.
This is a count() issue. I should be able to provide this count manually.
I know. Count() is slow. However, it's not necessary to query the count. The count can be retrived by other faster methods than count(*)
this is just laravel pagination dude
I just thought that filament have a quick solution for this
the query is coming from eloquent
what is the alternative
you can override anything you want in filament, including these queries
I suppose I'll try providing custom count value in plain laravel and try to get it work there. Maybe then do the same in filament.
how are you going to count the value
However, if it's possible to provide count manually (for pagination) in eloquent (idk, I'll give it a go), why filament doesn't have an option for this? One can easily define a function (e.g. getCount()) in the resource class.
im genuinely interested how you are going to count the rows any quicker
if there was a quicker way laravel would do it
hence why they have simple pagination as a feature
show table status like 'my_table';
SELECT TABLE_ROWS
FROM information_schema.tables
WHERE table_schema = 'my_db' AND table_name = 'my_table';
I can also cache it
The point is count(*) is slow on large tables. I can't use this on 50m table
There should be a way to provice the count manually for pagination
So you can use simple pagination to avoid the count as Dan has already said?
You can't get rid of count with standard pagination
I'm somewhat new to Laravel, and completely new to filament.
Since things seem to be quicker/practical in filament, I just hoped there was a way to do this quicker, like defining a method on the resource to return the count, or chain/call some method on the $table, idk.
Seeing that this (working with large tables) isn't handled by default, makes me think that this is not a standard issue?
I'm also trying to minimize the things I have to learn/deal with for standard things, so that's me being lazy, I guess.
It's not really a laravel issue either, it's how you are handling big datasets. If you have a large dataset and you want complex pagination then you have to count to get that pagination values. Once you count there is your issue. So the only solution I know off would be to use simple pagination instead as previously mentioned by myself and Dan.
You could in theory build a map table which has the counts in, but that's getting messy and then requires an observer to monitor the tables and then like a routine to ensure that count table is counted right at all times, like if someone plays with the db directly.