#php #mysql #laravel #laravel-query-builder
Вопрос:
Я хотел бы оптимизировать эти 3 запроса, которые позволяют мне отображать пользователей, разместивших наибольшее количество фотографий на нашем сайте, в соответствии с критериями даты (текущая неделя, прошлая неделя, с начала).
Время запроса измеряется в 1,5 секунды с помощью панели отладки, это действительно долго ! Знаете ли вы, как его оптимизировать ?
public function show() { $currentWeek = User::whereHas('pictures') -gt;whereHas('pictures', fn ($q) =gt; $q-gt;whereBetween('created_at', [Carbon::now()-gt;startOfWeek(), Carbon::now()-gt;endOfWeek()])) -gt;withCount(['pictures' =gt; fn ($q) =gt; $q-gt;whereBetween('created_at', [Carbon::now()-gt;startOfWeek(), Carbon::now()-gt;endOfWeek()])]) -gt;orderBy('pictures_count', 'DESC') -gt;limit(10) -gt;get(); $lastWeek = User::whereHas('pictures') -gt;whereHas('pictures', fn ($q) =gt; $q-gt;whereBetween('created_at', [Carbon::now()-gt;startOfWeek()-gt;subWeek(), Carbon::now()-gt;endOfWeek()-gt;subWeek()])) -gt;withCount(['pictures' =gt; fn ($q) =gt; $q-gt;whereBetween('created_at', [Carbon::now()-gt;startOfWeek()-gt;subWeek(), Carbon::now()-gt;endOfWeek()-gt;subWeek()])]) -gt;orderBy('pictures_count', 'DESC') -gt;limit(10) -gt;get(); $overall = User::whereHas('pictures') -gt;whereHas('pictures') -gt;withCount('pictures') -gt;orderBy('pictures_count', 'DESC') -gt;limit(10) -gt;get(); return view('users.leaderboard', [ 'currentWeek' =gt; $currentWeek, 'lastWeek' =gt; $lastWeek, 'overall' =gt; $overall, ]); } Комментарии:
1. вы пробовали создать
indexстолбец базы данных «created_at»?2. нет, никогда, не могли бы вы объяснить подробнее ? 🙂
3. да, конечно. индексы точно так же, как индексы перечислены в начале любой книги, чтобы найти элементы по их названиям на определенных страницах. База данных использует индексы для того же. например, у вас есть таблица, содержащая 1 000 000 данных, и вы запрашиваете поиск по имени «xyz». Таким образом, в обычном случае база данных будет искать » xyz «от начала до конца и будет просматривать все 1,00,000 записей, чтобы найти «xyz». Но если вы создадите индекс в столбце «имя», то база данных будет искать только там, где имя начинается с «x», и ей не нужно будет просматривать все 1 000 000 записей, и, таким образом, результат запроса будет намного быстрее
4. да, для доступа к большему количеству данных требуется больше времени
5. Хорошо, спасибо за вашу драгоценную помощь и за ваше время 😉
Ответ №1:
Во-первых, во-первых, вы дважды звонили whereHas по поводу отношений с фотографиями, так что вы можете избавиться от неквалифицированного звонка.
$currentWeek = User::whereHas('pictures', fn ($q) =gt; $q-gt;whereBetween('created_at', [now()-gt;startOfWeek(), now()-gt;endOfWeek()])) -gt;withCount(['pictures' =gt; fn ($q) =gt; $q-gt;whereBetween('created_at', [now()-gt;startOfWeek(), now()-gt;endOfWeek()])]) -gt;orderBy('pictures_count', 'DESC') -gt;limit(10) -gt;get(); Это уменьшает SQL-запрос от этого:
select `users`.*, ( select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null ) as `pictures_count` from `users` where exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `pictures`.`deleted_at` is null) and exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null) and `users`.`deleted_at` is null order by `pictures_count` desc limit 10 К этому:
select `users`.*, ( select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null ) as `pictures_count` from `users` where exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null) -- no second where exists clause and `users`.`deleted_at` is null order by `pictures_count` desc limit 10 Итак, у вас есть только одно условие в этом where пункте. Он выбирает пользователей, у которых есть фотографии в указанном диапазоне дат. Выглядит лучше, правда?
Но вы уже используете withCount с закрытием, поэтому вы учитываете только фотографии в диапазоне дат. Что произойдет, если условие не совпадает? Он возвращает ноль. Поскольку вы все равно выполняете обратную сортировку по количеству, другой вызов также whereHas может быть отправлен.
$currentWeek = User::withCount(['pictures' =gt; fn ($q) =gt; $q-gt;whereBetween('created_at', [now()-gt;startOfWeek(), now()-gt;endOfWeek()])]) -gt;orderBy('pictures_count', 'DESC') -gt;limit(10) -gt;get(); Теперь ваш SQL выглядит так:
select `users`.*, ( select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null ) as `pictures_count` from `users` where `users`.`deleted_at` is null -- no where exists clauses at all any more order by `pictures_count` desc limit 10 И он должен работать намного быстрее. Это несколько изменяет ваши данные; в результирующей коллекции всегда будет 10 элементов, даже если некоторые из них равны нулю. Если вы не хотите, чтобы нули были в вашей таблице лидеров, просто отфильтруйте их из коллекции.
Комментарии:
1. Большое вам спасибо за этот ответ! Я буду применять все это. Я буду применять все это. Спасибо!