Filament•6mo ago

Help understanding/troubleshooting slow queries

I'm using Filament v2 and have a table I've been trying to diagnose an issue with, which is slowness when querying the database that only occurs when using the Filament Resource/Table component with default individual column search. When I use the individual search column and supply two space-separated terms, the query is very slow. However, if I customize the search for that column and create the query manually, or if I run the slow query directly from \DB::select, the performance is great. I'm trying to figure out what would cause the slowness for this one set of conditions and I can't seem to figure it out. Any thoughts?

// Customized column search
->searchable(query: function (Builder $query, string $search): Builder {
    return $query->where('_raw', 'like', "%$search%");
}, isIndividual: true)

// Customized column search
->searchable(query: function (Builder $query, string $search): Builder {
    return $query->where('_raw', 'like', "%$search%");
}, isIndividual: true)

Query stats for the default search, default search ran from tinker/DB::select, and the customized column search.

[2024-05-31 12:37:02] local.INFO: query_log {"query":"select top 11 * from [syslogs] where ([_raw] like '%filter1%') and ([_raw] like '%filter2%') order by [id] desc","time":67506.94} -> default search
[2024-05-31 12:38:16] local.INFO: query_log {"query":"select top 11 * from [syslogs] where ([_raw] like '%filter1%') and ([_raw] like '%filter2%') order by [id] desc","time":75.9} -> from tinker
[2024-05-31 12:34:57] local.INFO: query_log {"query":"select top 11 * from [syslogs] where (([_raw] like '%filter1%')) and (([_raw] like '%filter2%')) order by [id] desc","time":67.97} -> custom search

[2024-05-31 12:37:02] local.INFO: query_log {"query":"select top 11 * from [syslogs] where ([_raw] like '%filter1%') and ([_raw] like '%filter2%') order by [id] desc","time":67506.94} -> default search
[2024-05-31 12:38:16] local.INFO: query_log {"query":"select top 11 * from [syslogs] where ([_raw] like '%filter1%') and ([_raw] like '%filter2%') order by [id] desc","time":75.9} -> from tinker
[2024-05-31 12:34:57] local.INFO: query_log {"query":"select top 11 * from [syslogs] where (([_raw] like '%filter1%')) and (([_raw] like '%filter2%')) order by [id] desc","time":67.97} -> custom search

2 Replies

toeknee•6mo ago

I woould suggest you use Telescope and monitor the entire query process for all things ran. You may find something is running multiple queries

hgmsOP•5mo ago

I have some middleware that logs every query and its execution time, which is what I had included above. The query times were crazy different for some reason and I did not figure out why. I ended up pivoting to Elastic since the dataset really needed something like that anyway.

Gaming

Programming

Help understanding/troubleshooting slow queries