Filament•2y ago

Advice on big tables

Good afternoon everyone! I'm using filament for a data science project for some medical research. And I currently have a table with a few million records. I'm having some issues with the fact it times out becouse the filtering takes longer then 60 seconds

14 Replies

DanielvdSpoelOP•2y ago

I have the following filters:

->filters([
                SelectFilter::make('clusterReport')->relationship('clusterReport', 'name'),
                Filter::make('cluster_size')
                    ->form([
                        Forms\Components\TextInput::make('minimum_size')->numeric()->default(2),
                        Forms\Components\TextInput::make('maximum_size')->numeric(),
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        return $query
                            ->when(
                                $data['minimum_size'],
                                fn (Builder $query, $date): Builder => $query->has('sequences', '>=', $data['minimum_size'])
                            )
                            ->when(
                                $data['maximum_size'],
                                fn (Builder $query, $date): Builder => $query->has('sequences', '<=', $data['maximum_size'])
                            );
                    }),
                Filter::make('cluster_similarity')
                    ->form([
                        Forms\Components\TextInput::make('minimum_similarity'),
                        Forms\Components\TextInput::make('maximum_similarity'),
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        return $query
                            ->when(
                                $data['minimum_similarity'],
                                fn (Builder $query, $date): Builder => $query->where('average_similarity', '>=', $data['minimum_similarity'])
                            )
                            ->when(
                                $data['maximum_similarity'],
                                fn (Builder $query, $date): Builder => $query->where('average_similarity', '<=', $data['maximum_similarity'])
                            );
                    })
            ])

->filters([
                SelectFilter::make('clusterReport')->relationship('clusterReport', 'name'),
                Filter::make('cluster_size')
                    ->form([
                        Forms\Components\TextInput::make('minimum_size')->numeric()->default(2),
                        Forms\Components\TextInput::make('maximum_size')->numeric(),
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        return $query
                            ->when(
                                $data['minimum_size'],
                                fn (Builder $query, $date): Builder => $query->has('sequences', '>=', $data['minimum_size'])
                            )
                            ->when(
                                $data['maximum_size'],
                                fn (Builder $query, $date): Builder => $query->has('sequences', '<=', $data['maximum_size'])
                            );
                    }),
                Filter::make('cluster_similarity')
                    ->form([
                        Forms\Components\TextInput::make('minimum_similarity'),
                        Forms\Components\TextInput::make('maximum_similarity'),
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        return $query
                            ->when(
                                $data['minimum_similarity'],
                                fn (Builder $query, $date): Builder => $query->where('average_similarity', '>=', $data['minimum_similarity'])
                            )
                            ->when(
                                $data['maximum_similarity'],
                                fn (Builder $query, $date): Builder => $query->where('average_similarity', '<=', $data['maximum_similarity'])
                            );
                    })
            ])

Does anyone have advise for how i can best handle this? Or is this just a thing of filtering is just not possible

Brian Kidd•2y ago

I’m not a filament expert but just confirm you have the proper database indices for columns you’re filtering on. Maybe worth using Debugbar and copy the query that’s being executed and run an explain on it in a database client to see what indices are being used. This is general DB stuff not specific to filament.

DanielvdSpoelOP•2y ago

Do you mean indexes?

Brian Kidd•2y ago

Yes

DanielvdSpoelOP•2y ago

Okay Yeah i don't have any indexes yet, is certainly something I can look into

Brian Kidd•2y ago

They’ll have a huge effect on performance - full table scans are expensive That’ll probably solve your problem

DanielvdSpoelOP•2y ago

Okay great! I think the problem is mostly within the fact i'm counting a relationship, and then filtereing on that count propably not the best i can do If i remove theat feature it works perfectly, I will keep it removed for now (since this is only a POC) and see if we will add it back later

Brian Kidd•2y ago

Makes sense. Is that a foreign key? If so it should be indexed but some folks don’t use foreign keys. Just make sure that’s indexed too. I sometimes cache counts like this but that creates other problems trying to keep it in sync. Good luck

DanielvdSpoelOP•2y ago

I do use foreign keys, caching shouldn't be to much of a problem since this data get's generated, and in theory, should never change afterwards would be a bit weird if data you base your research on changes Thanks for you advice!

Dan Harrin•2y ago

i dont think this is a filament issue, i agree with Brian if a query is taking a long time then it is a db schema issue

DanielvdSpoelOP•2y ago

I never wanted to imply this is a filament issue, i'm sorry for making it seem like this Guess by me opening a issue here i kinda did sorry 😂

Dan Harrin•2y ago

okay, i just assume you think it is by posting in the filament discord thats ok

DanielvdSpoelOP•2y ago

Do you 'cache' this in a seperate database column?

Brian Kidd•2y ago

Yes. I try to avoid this but sometimes it’s necessary. Make it a last resort

Gaming

Programming

Advice on big tables