PHP Laravel get millions record using MYSQL

Question

I have been using laravel + mysql for my project but and it was working perfectly fine until now. The records keep on increasing and now they have reached almost a million record. Problem is when i try to fetch sms from my database using this query

 $smsHistory = SmsLog::where('created_at', '>=', $startDate)->where('created_at', '<=', $endDate)->whereNotNull('gateway_statuscode')->get();

It gives a 500 error without showing anything in error log. What i assume is that as i decreased the time period it gives me record so the problem is the bulk it can not handle. What could be the possible solution as i have to give it today.

I am not worried about the error log.. i want to get a count of those million record but i want to apply some algorithm on it before doing it

This is the check which i have to perform afterwards to see how many sms are in sms log

 foreach ($smsHistory as $sms) {
        $sms_content = SmsService::_getSmsContent($sms);
        if ($sms_content->business_id && array_key_exists($sms_content->business_id, $smsCredits )) {
            if (floor(strlen($sms_content->content) / 160) == 0) {
                $smsCredits[$sms_content->business_id]['count'] += 1;
            }

            if (floor(strlen($sms_content->content) / 160) == 1 && floor(strlen($sms_content->content) / 306) == 0) {
                $smsCredits[$sms_content->business_id]['count'] += 2;
            }

            if (floor(strlen($sms_content->content) / 306) == 1 && floor(strlen($sms_content->content) / 459) == 0) {
                $smsCredits[$sms_content->business_id]['count'] += 3;
            }

            if (floor(strlen($sms_content->content) / 459) == 1 && floor(strlen($sms_content->content) / 621) == 0) {
                $smsCredits[$sms_content->business_id]['count'] += 4;
            }

            if (floor(strlen($sms_content->content) / 621) == 1 && floor(strlen($sms_content->content) / 774) == 0) {
                $smsCredits[$sms_content->business_id]['count'] += 5;
            }

            if (floor(strlen($sms_content->content) / 774) == 1 && floor(strlen($sms_content->content) / 927) == 0) {
                $smsCredits[$sms_content->business_id]['count'] += 6;
            }
        }

this is the database field

content is the sms that i have to get and count

Answer 1

Regarding the 500 error: If you are getting a 500 error, there should hopefully be some clue in the actual server error log (your laravel application error log may not have caught it depending on the error handlers, etc and what the cause was). php_info() should show you the location of the physical error log with the error_log setting:

<?php phpinfo(); ?>

If I had to guess, possibly something memory related that is causing it to choke. But that is just a guess.

As a side question, why are you trying to retrieve so many at once? Can you split them up somehow?

Edit based on updated question: You may need to use some raw-expressions here to get what you really want: https://www.laravel.com/docs/4.2/queries#raw-expressions

MySQL for example provides the ability to get a column length: FLOOR(CHAR_LENGTH(content) / 160) as len1, FLOOR(CHAR_LENGTH(content) / 306) as len2, FLOOR(CHAR_LENGTH(content) / 459) as len3

So probably with some magic we could take your query and let the database system do it all for you. And there are probably more efficient ways to do it, if I knew more about the significance of those numbers, but I am trying to help you at least get on one possible path.

Answer 2

You should setup index for mysql, or implement a search engine like elastic search