Laravel Performance question

@alexgodbehere79 creating indexes on filter fields will improve your performance.

https://laravel.com/docs/8.x/migrations#indexes

Hey @automica

Thank you for your response. I have been using it.

Do you think my performance for fetching data is standard already:?

1). indexing

2). select('field1', 'field2') instead of select(*)

3). avoid N+1 problem

example, do not use delete inside loop, use delete in(1,2,3) instead

4). hardware

5). check true or false is faster than string == "something"

where('active', 1) --- always before--- where('name', 'Peter')

6). avoid use count() when you want to check true or false

filled($var) / $var->exists / etc

and many many

@newbie360 thank you for your comment.

Actually I follow almost everything that you have mentioned.

My question is, does this time frame and memory uses for fetching such amount of data is standard or not.

@alexgodbehere79 response time will depend on the spec of your hardware and how you are running your app.

eg PHP /MySQL native on windows runs slower than running homestead on the same hardware.

you are not fetching a lot of data, you are just loading 100 records because of pagination

Indexing is critical to the speed, and your figures seem SLOW

@alexgodbehere79 what hardware are you on? Is this remote or local?

I don't think most people actually sit there and paginate that many records, get in the habit of doing a query of what's needed.

Perhaps get 1000 and paginate. Have drill down for further research. I do this often for things like accounts receivable. ...

For such reports I have a quick summary, if more detail needed a button to click to show:

COMPANY A in a header

------  A paginated list of their receivables

A link (next company) also

In other words set up double pagination as needed.

Try it yourself, go to yahoo.com punch in "dogs" 433,000,000 results

I don't think any human will actually paginate through all of that. It would probably take a year.

By the way big searches use big table with special indexing. Look up the technology Google, Yahoo, etc uses.

Edit: Usually pagination on huge results, you would use the

id > last_id ORDER BY id LIMIT NN;  technique

You should really read up of index techniques used in industry used on large result sets.

@alexgodbehere79

Hey, no doubt that 5M is a big amount of data. Ofcourse, it's not a wise idea to dump your data at a time. Now the question is how do you want to slice your data?

Recently I was working with such kinds of things. I tried few options like paginate() chunk() cursor() and simplePaginate().

I found the best solution for me is the simplePaginate because of it take less memory and time to load probably because of it uses lazyload. I was working with around 2M data, where I somehow able to load them by simplePaginate in ~6s. The only drawback is you cannot count the total of the matched records directly on it.

Again, it's always depends on the indexing, caching and the hardware that others suggested already.