Как правильно выполнить выборку?

Question

[entermix]

Допустим нам нужно сформировать массив для последующего вывода в таблицу статистики сайта:

$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(),
                ],
            ],
        // ...
    ];

Как видим, для поиска применяется объект, который достается с БД, а следовательно это + 1 запрос на каждую выборку:

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

Таких параметров в $data будет много (users, email, sms, etc..) в данный момент получается 500+ запросов, использоваться все будет для админки, т.е. никакой нагруженности, собственно как более правильно организовать все?

Сделать такие запрос для and_where перед формированием массива (чтобы он выполнялся всего 1 раз), или забить на все просто включив кеширование MySQL?

Answer 1

[Melkij]

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

Аналогично и первая толпа запросов сворачивается в один простой SQL.

А с учётом сущности данных - имеет смысл их перенести в отдельную таблицу аггрегации и не пересчитывать в рантайме весь массив.

Comments

[entermix]

Спасибо. А прокомментируйте, пожалуйста, кеширование на стороне mysql сервера.

[Melkij]

Вы про Query cache? Удивительное явление.

Инвалидироваться кэш будет на любой insert/update/delete таблицы. Т.е. кэш запросов к таблице Sms будет адекватно работать, только если админку открывают очень часто, а сама таблица изменяется очень редко. Т.е. картина, в корне отличная от вашей.
Если бы у вас была таблица новостей сайта, которые пишут полтора землекопа раз в день, а остальные делают только select - то query cache бы работал внятно.

[entermix]

Melkij: но ведь по идее кэширование будет адекватно работать с таблицей статусов? По сути это справочники, и менять их никто не будет, а таких запросов в данном случае много (запрос id по имени статуса)

[Melkij]

Но зачем вам их много?
Прочитайте один раз весь список статусов в словарь и всё. И даже код сразу станет компактнее.