Filament•13mo ago

Searching in big database results in timeout

Hello, when searching in my table I keep running into timeouts. The exact error is "Maximum execution time of 60 seconds exceeded" at the return statement of the Database\Connection.php The database has a field uuid which contains an uuid. When I write a manual select statement it takes about 0.8s to get the results. I also tried in another Laravel project and the queries seem to execute quickly. This is the code for the table function

    public static function table(Table $table): Table
    {
        return $table
            ->columns([
                Tables\Columns\TextColumn::make('time')
                    ->dateTime("d.m.Y - H:m:s")
                    ->sortable(),
                Tables\Columns\TextColumn::make('type')
                    ->numeric(),
                Tables\Columns\TextColumn::make('uuid')
                    ->label('UUID')
                    ->copyable()
                    ->searchable(isIndividual: true),
            ])
            ->actions([
            ])
            ->bulkActions([
            ])
            ->emptyStateActions([
            ])
            ->deferLoading();
    }

    public static function table(Table $table): Table
    {
        return $table
            ->columns([
                Tables\Columns\TextColumn::make('time')
                    ->dateTime("d.m.Y - H:m:s")
                    ->sortable(),
                Tables\Columns\TextColumn::make('type')
                    ->numeric(),
                Tables\Columns\TextColumn::make('uuid')
                    ->label('UUID')
                    ->copyable()
                    ->searchable(isIndividual: true),
            ])
            ->actions([
            ])
            ->bulkActions([
            ])
            ->emptyStateActions([
            ])
            ->deferLoading();
    }

I also tried giving it a custom query analogue to what I did in the other laravel project (it really just is a where statement), producing this

                    ->searchable(query: function (Builder $query, string $search): Builder {
                        return $query->where('uuid', $search);
                    }, isIndividual: true),

                    ->searchable(query: function (Builder $query, string $search): Builder {
                        return $query->where('uuid', $search);
                    }, isIndividual: true),

Have I missed something where I could've changed something? (I am in my early php stages) What I just found out through some more testing is that the search works fine when I use a uuid that in the first couple of hundred (or thousand for that matter) rows, but using one that is "further away" seems to hang the search entirely. Thanks in advance for any pointers

Solution:

Ah I found the "issue" Selecting from the database is very quick, but not if you add order by xxx asc...

Jump to solution

11 Replies

Arnold Schwarzenegger•13mo ago

Hmm this can be a lot of things 1) Install Laravel debugbar to see which query is actually the slowest. It might be that you're running into a n+1 issue 2) Disable pagination on the table to see if it helps 3) Relevant to the 1st point, check pre-load any relationships you might be using You don't need debugbar if you're familiar with other ways of profiling your app's queries Out of curiosity, how many rows are we talking about here?

Samir•13mo ago

I face similar issue when I try to attach record in Relationship manager while using Select Serach. It times out. And when I use pre loading then it goes in infinite wait in loading the button.

QuOP•13mo ago

The last query I see in debugging is just an aggregate query that does count(*) on the table with the filter I set, that query runs in 0.something ms I will try it w/o pagination later Currently I do not have any relationships configured but I will check The initial amount of rows is quite large but once you set the uuid filter it is down to like 170k rows

Dennis Koch•13mo ago

The important question: Is the query fast without Filament?

QuOP•13mo ago

Yes, in my only laravel project the query with a $query->where('uuid', $uuid) runs in under a second and if I do a direct SELECT * FROM xxx WHERE uuid="..." it also runs in under a second

Solution

Qu•13mo ago

Ah I found the "issue" Selecting from the database is very quick, but not if you add order by xxx asc

QuOP•13mo ago

No I probably only need to figure out how to remove that from the query Changing the default sort did the trick

Dennis Koch•13mo ago

You are missing an index then.

QuOP•13mo ago

The table does have an index 🤔

Dennis Koch•13mo ago

Yeah but probably not the right one if you have a default sorting. One index isn’t enough if you want fast sorting for different orders, filters etc.

QuOP•13mo ago

I changed it to sort by a timedate column since sorting by the id doesn't make sense in this case.

Gaming

Programming

Searching in big database results in timeout