Filament•8mo ago

How to speed up the search function on a table with relationships

I have a table with ~300 records and searching the table in production takes close to 10 seconds. Two of the searchable() columns are relationships. I have a different resource with a similar number of rows (but no relationships) and searching takes less than 1 second. The docs say that relationships are already eager loaded by default. Could I have accidentally disabled eager loading somehow? Is there anything I can do to speed up the search process with relationships?

Solution:

maybe it's the inner select that's slow then 🤔 Anyway, this is probably not a filament issue. You can try to modify your search query to make it faster or try other ways (eg create a custom view etc)

Jump to solution

20 Replies

Dennis Koch•8mo ago

Eager loading is not relevant here. Searching always happens on the DB level. If things are slow your are probably missing an index.

bwurtz999•8mo ago

Can you please explain more? Both tables have primary ID indexes

ModestasV•8mo ago

Primary ID index is one thing. But the columns you do the search on - also need an index. Otherwise database performs full text search each time, which is slow For example: You have name/last name. And people can search in them, so these columns need to have an index on them. As that will speed up the search proccess by a lot. So my suggestion would be - make a local copy (if possible!) and play around directly in the database. Try to add indexes and see if it improved. If that worked - you can make migrations, re-import your db copy, test and then push to live

bwurtz999•8mo ago

Thanks for the explanation!

ChesterS•8mo ago

Just keep in mind that indexes for text searches are not always as straightforward as others, especially if you're doing %like% searches. But yeah. plugins like Laravel Debugbar can help you identify slow-running queries so you can try to optimise them.

bwurtz999•8mo ago

So I've added an index to a varchar column and it has helped, but I still have a slow query. How do I try and optimize it? Also - looking at the logs of my queries I actually have two slow queries. One is just getting the count of records that will be returned, the other gets the actual results Both are tagged as slow on Telescope (both take between 250 and 300ms locally)

ChesterS•8mo ago

Yeah that's because of the pagination. It's a known fact/issue. Can you simplify/optimise the slow query?

bwurtz999•8mo ago

So it looks like this issue is that one query is actually two queries:

select
  count(*) as aggregate
from
  `items`
where
  `category_id` in (6, 7, 8, 9, 10, 26, 27, 28)
  and (`items`.`deleted_at` is null)
  and (
    `name` like '%turke%'
    or exists (
      select
        *
      from
        `market_items`
      where
        `items`.`id` = `market_items`.`item_id`
        and `barcode` like '%turke%'
        and `market_items`.`deleted_at` is null
    )
  )

select
  count(*) as aggregate
from
  `items`
where
  `category_id` in (6, 7, 8, 9, 10, 26, 27, 28)
  and (`items`.`deleted_at` is null)
  and (
    `name` like '%turke%'
    or exists (
      select
        *
      from
        `market_items`
      where
        `items`.`id` = `market_items`.`item_id`
        and `barcode` like '%turke%'
        and `market_items`.`deleted_at` is null
    )
  )

ChesterS•8mo ago

Also 300ms is not ideal but not crazy - depending on the data. You said it takes 10 seconds - where does the rest of the delay come from?

bwurtz999•8mo ago

This is locally Adding an index and removing one column as searchable dropped the time from roughly 1100ms to 650ms in production it takes closer to 8sec on average so it'll make it better

ChesterS•8mo ago

Yeap. My bet is it's the barcode like '%turke%' query. Also have a look at this, https://filamentphp.com/community/danharrin-fast-table-pagination might help with pagination

bwurtz999•8mo ago

Yeah I bet so too. For comparison on time, for a table without any relationship columns the query time is 10ms with a similar number of records (locally) The relationship search is 25x slower... Really appreciate the community link. I'll definitely try this out

ChesterS•8mo ago

Hmm you're using a whereHas in your query, right?

bwurtz999•8mo ago

No I don't think so just a where I use a whereIn with the modifyQueryUsing

ChesterS•8mo ago

Ok I just asked because this looks like a query generated by whereHas on a relation. You might be able to improve it with a custom query but I'm not good enough at SQL to help with that. Out of curiosity, how many records are there in the items and market_items tables?

bwurtz999•8mo ago

Locally, 2500 in each but the whereIn shrinks it to 500 *to 500 for the query in question

Solution

ChesterS•8mo ago

bwurtz999•8mo ago

Yeah you're probably right Appreciate the help!

bwurtz999•8mo ago

For anyone who encounters this problem later, I found that using this plugin (https://github.com/webbingbrasil/filament-advancedfilter) made my queries much faster

GitHub

GitHub - webbingbrasil/filament-advancedfilter: A collection of eas...

A collection of easy-to-use filters with clause conditions to Filament - GitHub - webbingbrasil/filament-advancedfilter: A collection of easy-to-use filters with clause conditions to Filament

bwurtz999•8mo ago

You don't get to use the nice search bar in the table, but it works

Gaming

Programming

How to speed up the search function on a table with relationships