How to properly sample?

Question

MySQL

Let's say we need to form an array for subsequent output to the site statistics table:

$data = [
    // ...
    'sms' => [
                'new' => [
                    'today' => ORM::factory('Sms')
                        ->where('created', '>', mktime(0, 0, 0, date('m'), date('d'), date('Y')))
                        ->and_where('status_id', '=', ORM::factory('Sms_Status')
                                ->where('name', '=', 'new')->find()
                        )
                        ->count_all(),
                    'yesterday' => ORM::factory('Sms')
                        ->where('created', '<', mktime(0, 0, 0, date('m'), date('d'), date('Y')))
                        ->and_where('created', '>', mktime(0, 0, 0, date('m', time() - Date::DAY), date('d', time() - Date::DAY), date('Y', time() - Date::DAY)))
                        ->and_where('status_id', '=', ORM::factory('Sms_Status')
                                ->where('name', '=', 'new')->find()
                        )
                        ->count_all(),
                    'hour' => ORM::factory('Sms')
                        ->where('created', '>', time() - Date::HOUR)
                        ->and_where('status_id', '=', ORM::factory('Sms_Status')
                                ->where('name', '=', 'new')->find()
                        )
                        ->count_all(),
                    'day' => ORM::factory('Sms')
                        ->where('created', '>', time() - Date::DAY)
                        ->and_where('status_id', '=', ORM::factory('Sms_Status')
                                ->where('name', '=', 'new')->find()
                        )
                        ->count_all(),
                        
                        // ...
                        
                    'all' => ORM::factory('Sms')
                        ->and_where('status_id', '=', ORM::factory('Sms_Status')
                                ->where('name', '=', 'new')->find()
                        )
                        ->count_all(),
                ],
                'sent' => [
                        // ...

                    'all' => ORM::factory('Sms')
                        ->and_where('status_id', '=', ORM::factory('Sms_Status')
                                ->where('name', '=', 'sent')->find()
                        )
                        ->count_all(),
                ],
                'not_sent' => [
                        //...
                        
                    'all' => ORM::factory('Sms')
                        ->and_where('status_id', '=', ORM::factory('Sms_Status')
                                ->where('name', '=', 'not_sent')->find()
                        )
                        ->count_all(),
                ],
            ],
        // ...
    ];

As you can see, an object is used for the search, which is obtained from the database, and therefore this is + 1 request for each selection:

->and_where('status_id', '=', ORM::factory('Sms_Status')
                                ->where('name', '=', 'not_sent')->find()

There will be a lot of such parameters in $data (users, email, sms, etc..) at the moment there are 500+ requests, everything will be used for the admin panel, i.e. no load, actually how to organize everything more correctly?
Make such a query for and_where before forming the array (so that it is executed only 1 time), or score on everything just by enabling MySQL caching?

Answer 1

select st.name, count(0) from Sms join Sms_status st on status_id = st.id where st.name in ('sent','not_sent', 'new') group by status_id

Similarly, the first crowd of queries is folded into one simple SQL.
And taking into account the nature of the data, it makes sense to transfer them to a separate aggregation table and not recalculate the entire array in runtime.