Bad performance when searching

[MrYamous]

Hello community

I've recently started using Powergrid to replace another lib but i'm experiencing some performance issues when using the search.

I need to search across relationship and two huges requests are made with a lot of LIKE %search% for searcheable fields which lead to 50 seconds requests.

select count(*) as aggregate from TABLE where ( (TABLE.motif_refus_manager` LIKE '%cindy%')

or exists (select * from JOINED_TABLE where TABLE.field = JOINED_TABLE.field and searcheableField1 LIKE '%searchterm%')

or exists (select * from JOINED_TABLE where TABLE.field = JOINED_TABLE.field and searcheableField2 LIKE '%searchterm%')

or exists (select * from JOINED_TABLE where TABLE.field = JOINED_TABLE.field and searcheableField3 LIKE '%searchterm%')

or exists (select * from JOINED_TABLE where TABLE.field = JOINED_TABLE.field and searcheableField4 LIKE '%searchterm%')

or exists (select * from JOINED_TABLE where TABLE.field = JOINED_TABLE.field and searcheableField5 LIKE '%searchterm%')
)`

I think this request is terrible and it's normal that it takes time, but then I ask myself, what can I do to improve this ?
Maybe it's coming from my datasource, currently i'm using Model::query()
Do I have to do a clean query in my datasource to handle a better search request ?

[luanfreitasdev]

Hello! You can improve performance by trying the following approaches:

• Remove the "searchable" method from any columns in the component that don’t need it.
• Ensure your query is optimized by avoiding unnecessary relationships and using efficient joins (e.g., inner joins).
• Avoid calling relationships directly in-line within your query if not required.
• Monitor the database latency of the server for potential bottlenecks.
• Consider adding composite keys, especially when using inner joins, to enhance query performance.
• If you're dealing with thousands of records, consider switching to more performance-oriented search engines like Typesense or MeiliSearch, better suited for large datasets.

[MrYamous]

Hello,

Currently I have 2K records which is obviously not a lot, but still leads to 50 seconds search requests. But I can't see how to improve the query, it's generated as I copied it

Will the way I use datasource method influence the search query?