Filament•2y ago

Improve search performance

How can I optimize search performance for multiple columns in Filament when dealing with a table containing over 700,000 records?

return $table
    ->columns([
        Tables\Columns\TextColumn::make('id')
            ->label('ID')
            ->searchable(),
        Tables\Columns\TextColumn::make('name')->searchable([
            'first_name',
            'last_name',
        ]),
        Tables\Columns\TextColumn::make('email')->searchable(),
    ]);

return $table
    ->columns([
        Tables\Columns\TextColumn::make('id')
            ->label('ID')
            ->searchable(),
        Tables\Columns\TextColumn::make('name')->searchable([
            'first_name',
            'last_name',
        ]),
        Tables\Columns\TextColumn::make('email')->searchable(),
    ]);

21 Replies

Dennis Koch•2y ago

Use a virtual column instead of two separate columns and put an index on that one

Vincent KlaiberOP•2y ago

Combining the three columns into one index?

Dan Harrin•2y ago

or use Scout with a real search driver instead of mysql

Vincent KlaiberOP•2y ago

Is it possible to provide a custom query for the search field without having to add it for each column?

Dennis Koch•2y ago

Just for search? I don't think so.

Dan Harrin•2y ago

how would you do this efficiently in normaly SQL? because its exactly the same in filament

Vincent KlaiberOP•2y ago

Is there a way to disable pagination for a resource table, as it appears to use a lot of resources?

Dan Harrin•2y ago

disabling pagination would make it 100x worse?

Vincent KlaiberOP•2y ago

I want to limit the response but prevent the SELECT count(*) AS aggregate FROM users WHERE query from running. I'm guessing the count(*) is used for the table pagination navigation.

Dan Harrin•2y ago

switch to simple pagination?

Vincent KlaiberOP•2y ago

tried but could not find a way how to switch to simple navigation on a resource table?

Dan Harrin•2y ago

what did you try

Vincent KlaiberOP•2y ago

https://filamentphp.com/docs/2.x/tables/getting-started#simple-pagination do I have to write a custom table component in order to enable simple pagination on my UserResource?

Dan Harrin•2y ago

no put that code on the List page class

Vincent KlaiberOP•2y ago

thanks Dan it seems it stil performce the count() query I will try another way to disable the pagination navigation

Dan Harrin•2y ago

it will only do the count if All is selected disabling pagination will make it so much worse.

Vincent KlaiberOP•2y ago

I dont understand how it will make it worse? I only want to fetch 20 records based on the search query and the user should not be able to paginate to the next result page.

Dan Harrin•2y ago

it will kill performance when there is no filter or search

Vincent KlaiberOP•2y ago

Is there a way to not perform the count query?

Dan Harrin•2y ago

https://filamentphp.com/tricks/fast-table-pagination

Filament

Fast table pagination by Dan Harrin - Tricks - Filament

Filament is a collection of tools for rapidly building beautiful TALL stack apps, designed for humans.

Vincent KlaiberOP•2y ago

Thanks Dan! Really appreciate you taking the time 🙇‍♂️

Gaming

Programming

Improve search performance