F
Filament•2mo ago
khairulazmi_

How to optimize usage of searchable().

Hello filament member . I have this code . 
>columns([
                TextColumn::make('index')
                    ->label('No.')
                    ->rowIndex(),
                ImageColumn::make('photo')->label('')->circular(),
                // ImageColumn::make('user.avatar')
                //     ->label('')
                //     ->circular(),
                TextColumn::make('fullName')
                    ->label('Full Name')
                    ->searchable(['firstname', 'lastname'])
                    ->placeholder('N/A'),
               TextColumn::make('designation')
                    ->label('Designation')
                    ->placeholder('N/A')
                    ->searchable()
                    ->toggleable(isToggledHiddenByDefault: true),
                TextColumn::make('phone')
                    ->label('Phone No')
                    ->placeholder('N/A')
                    ->searchable()
                    ->toggleable(isToggledHiddenByDefault: true),
                TextColumn::make('mobile')
                    ->label('Mobile')
                    ->placeholder('N/A')
                    ->searchable()
                    ->toggleable(isToggledHiddenByDefault: true),
>columns([
                TextColumn::make('index')
                    ->label('No.')
                    ->rowIndex(),
                ImageColumn::make('photo')->label('')->circular(),
                // ImageColumn::make('user.avatar')
                //     ->label('')
                //     ->circular(),
                TextColumn::make('fullName')
                    ->label('Full Name')
                    ->searchable(['firstname', 'lastname'])
                    ->placeholder('N/A'),
               TextColumn::make('designation')
                    ->label('Designation')
                    ->placeholder('N/A')
                    ->searchable()
                    ->toggleable(isToggledHiddenByDefault: true),
                TextColumn::make('phone')
                    ->label('Phone No')
                    ->placeholder('N/A')
                    ->searchable()
                    ->toggleable(isToggledHiddenByDefault: true),
                TextColumn::make('mobile')
                    ->label('Mobile')
                    ->placeholder('N/A')
                    ->searchable()
                    ->toggleable(isToggledHiddenByDefault: true),
15 Replies
khairulazmi_
khairulazmi_OP•2mo ago
When I search . Right now when the query sql query is this : 
select * from `account` where `company_id` = 258 and exists (select * from `employee` where `account`.`id` = `employee`.`account_id` and `employee_status_id` = 1) and (`firstname` like '%lisa%' or `lastname` like '%lisa%' or ((exists (select * from `employee` where `account`.`id` = `employee`.`account_id` and `employee_status_id` like '%lisa%'))) or exists (select * from `employee` where `account`.`id` = `employee`.`account_id` and `employee_no` like '%lisa%') or (exists (select * from `user_role` where `account`.`user_role_id` = `user_role`.`id` and `name` like '%lisa%')) or `designation` like '%lisa%' or `phone` like '%lisa%' or `mobile` like '%lisa%' or exists (select * from `user` where `account`.`user_id` = `user`.`id` and `email` like '%lisa%') or exists (select * from `user` where `account`.`user_id` = `user`.`id` and `username` like '%lisa%') or exists (select * from `employee` where `account`.`id` = `employee`.`account_id` and `address` like '%lisa%') or `created_time` like '%lisa%' or `updated_time` like '%lisa%') order by `account`.`id` asc limit 10 offset 0
select * from `account` where `company_id` = 258 and exists (select * from `employee` where `account`.`id` = `employee`.`account_id` and `employee_status_id` = 1) and (`firstname` like '%lisa%' or `lastname` like '%lisa%' or ((exists (select * from `employee` where `account`.`id` = `employee`.`account_id` and `employee_status_id` like '%lisa%'))) or exists (select * from `employee` where `account`.`id` = `employee`.`account_id` and `employee_no` like '%lisa%') or (exists (select * from `user_role` where `account`.`user_role_id` = `user_role`.`id` and `name` like '%lisa%')) or `designation` like '%lisa%' or `phone` like '%lisa%' or `mobile` like '%lisa%' or exists (select * from `user` where `account`.`user_id` = `user`.`id` and `email` like '%lisa%') or exists (select * from `user` where `account`.`user_id` = `user`.`id` and `username` like '%lisa%') or exists (select * from `employee` where `account`.`id` = `employee`.`account_id` and `address` like '%lisa%') or `created_time` like '%lisa%' or `updated_time` like '%lisa%') order by `account`.`id` asc limit 10 offset 0
how do i improve this ? can i join table ? or did i do something wrong .
khairulazmi_
khairulazmi_OP•2mo ago
No description
khairulazmi_
khairulazmi_OP•2mo ago
Right now it take around 25second . just for search ..
No description
toeknee
toeknee•2mo ago
So firstly have you got the following indexes? 
INDEX idx_company_id ON account(company_id);
INDEX idx_account_id ON employee(account_id);
INDEX idx_employee_status ON employee(employee_status_id);
INDEX idx_employee_no ON employee(employee_no);
INDEX idx_user_role ON user_role(id, name);
INDEX idx_user_email ON user(email);
INDEX idx_user_username ON user(username);
INDEX idx_company_id ON account(company_id);
INDEX idx_account_id ON employee(account_id);
INDEX idx_employee_status ON employee(employee_status_id);
INDEX idx_employee_no ON employee(employee_no);
INDEX idx_user_role ON user_role(id, name);
INDEX idx_user_email ON user(email);
INDEX idx_user_username ON user(username);
khairulazmi_
khairulazmi_OP•2mo ago
yes i have.
toeknee
toeknee•2mo ago
You can customise the query in the searchable method if you want to improve that side of it, good examples here https://laracasts.com/discuss/channels/filament/customize-the-search-query-in-the-textcolumn-searchable you can also modify the table query to load in the relationships too.
Laracasts
khairulazmi_
khairulazmi_OP•2mo ago
I will try .. But can i ? or i should not put searchable() on every columm ? @toeknee .
toeknee
toeknee•2mo ago
I use searchable() on every column absolutely fine. So I am surprised it takes so long tbj. If you don't place it on the columns you can't search the way you want .
khairulazmi_
khairulazmi_OP•2mo ago
Thank you . I will try first 🙂
ChesterS
ChesterS•2mo ago
Depending on your data, you will run into performance issues. We have a table with around 1.5M records, and searching is a pain since LIKE queries cannot use indexes. Look into fulltext indexes etc and/or generated columns that might help you. Honestly, at this point this is beyond the scope of filament and just a genral optimisation problem. Have fun!
toeknee
toeknee•2mo ago
In theory if it is that big you could build a custom view table which is basically a highly opitmised, pre-defined table for a specific model that just reads data. I've done that for a transactions table as we had to merge two datasets and it streamlines eloquent
khairulazmi_
khairulazmi_OP•2mo ago
Did you mean to use sql views?
toeknee
toeknee•2mo ago
Yeah
khairulazmi_
khairulazmi_OP•2mo ago
We can connect eloquent with sql views ?
toeknee
toeknee•2mo ago
Of course just as normal However, it's only for reading

Did you find this page helpful?