Filament•5mo ago

Count slow query

Doing the count of the filtered results is pretty slow. However, it's needed in our use case. So it's just the cost of doing business right? I don't know any way you would speed that up.

14 Replies

Mack HOP•5mo ago

This one has no filtering which is obviously faster.

Matthew•5mo ago

have you put an index on the column? BTW, this board is for filament, not general coding queries. No need for lower operator. Also, search for right 4 characters, rather than making it go through the entire filename.

awcodes•5mo ago

What is the actual eloquent query? Or are you actually doing a raw query?

Tonkawuck•5mo ago

Might see a speed improvement if file extensions are placed in a new string column and indexed.

awcodes•5mo ago

That’s why I want to se the actual query. I think the count(*) is slowing it down. I could certainly be wrong though. Just a gut feeling. Any query against a string parsing will definitely be slower though. It not something that can really be indexed.

Matthew•5mo ago

"Might"😆

Mack HOP•5mo ago

@Matthew yes I have indexes

-- Indices
CREATE INDEX idx_originals_dir ON public.originals USING btree (dir);
CREATE INDEX idx_originals_filename ON public.originals USING btree (filename);
CREATE INDEX idx_originals_callno ON public.originals USING btree (callno);

-- Indices
CREATE INDEX idx_originals_dir ON public.originals USING btree (dir);
CREATE INDEX idx_originals_filename ON public.originals USING btree (filename);
CREATE INDEX idx_originals_callno ON public.originals USING btree (callno);

@awcodes It's just the query I pulled out of ray with showQueries. It's just as slow if I run it in TablePlus. I don't know that I have any options, but I'm looking. Aslo it's Postgres. If that matters to anyone.

Tonkawuck•5mo ago

@Mack H Write a script that extracts the file extension pathinfo($path, PATHINFO_EXTENSION) into a new column for all rows in your database. I like to use a Laravel Command for this type of thing. Index this column. Use this new column for the query.

Matthew•5mo ago

What column type have you got filename as ? Your index won't be being utilised btw. Your requesting a substring search on a btree index, it has to table scan that to check every row. Also, filename columns tend not to be short. There are a few options. 1) You could implement full text indexing. But that would be overkill and also wouldn't guarantee success 2) You could create an additional filename_extension column, which could be indexed, and ensure your app is not wildcard searching, then handle the population of that at your app level. 3) What I would do, create a computed filename_extension column and throw an index on that.

Mack HOP•5mo ago

The problem is I was using .tif as the example, but in reality it could be "blah". So wildcard is going to the be used the most. The reality is the actual query is fast, but the count is slow. The numbers bare that out. Thank you for the all the input.

awcodes•5mo ago

If the query if fast just run ->count() on the collection instead.

Mack HOP•4mo ago

That count query comes from a resource index. I'm not sure I can customize it. An idea would be only run count when a filter is applied.

Mack HOP•4mo ago

Thttps://www.youtube.com/watch?v=Btb6Vyh7dr0

Filament Daily

YouTube

Filament Table Performance: Avoid One count(*) Query

An interesting "investigation" we've performed based on one recent comment.

Mack HOP•4mo ago

That pretty much encapsulates my problem.

Gaming

Programming

Count slow query