Level 21
@irfan Pagination would be best to display the records.
Mar 28, 2015
7
Level 1
Fetch large mysql data in php
Hi everyone,
I am using Kohana for my app. I have of lacs of records in few table. More often I need to fetch that data to show the user how they are doing. What is the best way to fetch thousands of mysql rows and then process them in php. Any suggestion would be appreciated.
PS: I am using Kohana query builder.
Level 8
Not sure if we understand the question better, you'd like to display every records or just a summary of the users progress. for something as simple use Pagination if you want to display something like for reports, just use mysql's functions, avg,sum or something that is useful and inbuit itself in MySQL.
Level 1
ohh I am sorry the way I have put my question. Let me rephrase my question. I have a cron job and I need to create invoices for each user and there are more than 2000 users in my app. So, I need to pull all the transactions for all the users and these transaction can be sometimes more than 80k for each users. Having said that I am not using any join it is a simple select query with indexes in places.
So, my concern was. Is it wise? to load all 80k records in php and do some calculations and finally store them in another table that is what I need to do.
Limit is one thing which we can use. I was wondering is there any other way to do this.
Thanks for ur time :)
Level 1
@KbirMhrjn @SachinAgarwal can u please check my new explanation. I tried to improve my question. Thanks for ur time guys
Level 21
@irfan if its like 80K records, then I would do something like this:
- Create a table to store the calculated values.
- Now Every time I insert a new record, I will recalculate the value with respect to the previous value stored in calculated values table.
- Now I will just fetch the calculated values from this table.
Level 1
@SachinAgarwal but how u will load ur 80k records first that is my main concern Load all 80k in single query or use limit or something else. That is basically my concern. What would be proper way for fetching these 80k records.
Level 21
@irfan If you have to calculate something from all the 80k records, then fetch them all in single query.
And if you follow my process, You will never need to fetch all 80k records, you will just need to fetch the previously calculated value.
And if you are interested in applying my process, let me know, I will tel you how can you do it even at this stage when you already have records in ur database.
Please or to participate in this conversation.