Search data from 15 fields and more than 28,500,000 entries

[munjaldevelopment]

I have one table with more than 28,500,000 entries & when I search data using common search, It took more than 5 minutes to search. How to make the search faster?

[pxpm]

Hello @munjaldevelopment

that's a tricky question to answer without knowing the sql you are running.

You can have a table with just a couple thousand records and take ages to search through.

From my experience, the main pain points are:

• inefficiently written queries: loading more columns than the ones that are necessary, loading entries using nested loops etc.
• lack of table indexes: all the columns where you do a ->where(column) or similar operation, should have an ->index() in the migrations.

Sorry I can't help much here.

Cheers

[abenzakour]

This is one of the most common issues for a CRUD Generators, the only way to manage this kind of table is to use the standard laravel;

Also if possible to use postgresql instead of mysql; it’s better

[pxpm]

Hey @abenzakour thanks for the input.
We use standard laravel under the hood. Having proper indexes for the columns your users may search and optimizing your queries to don't return more than needed information goes out of scope for us but lies on your (developer) side.

Imagine you have a table with 10 basic text columns. The reason why you don't have to do "anything else" for those basic text columns search to work is because for each of them we add a ->where(...) when you search the table.
If you (developer) only added 2 indexes on those columns, the other 8 columns would be doing un-optimized queries, and most of the time not needed because you only use the 2 columns you indexed to search that table. So you should set searchLogic => false on columns you don't want to be searchable.

This is not noticeable in some thousand entries, but in millions ? Selecting one more column leads to increased times as it does searching on columns that are not indexed.

If you have a heavy searched table, you can implement some caching mechanism by overwriting the search() function in your controller.

I think the same optimizations you would do in a Laravel application without an "admin panel generator" it's the same you would do when using Backpack.

Let me know if I can help you guys with something else. If you find Backpack doing something it shouldn't, or not doing something it should, let us know 👍

Cheers