Как быстрее выбирать записи из базы данных laravel?

Question

[Ismail-1]

Есть таблица в БД где хранятся объявления по аренде недвижимости

kvartiras

id | tip_obekta | cena | etag | etagnost | и тд. Ещё около 20 полей.
1 | Квартира | 1000 | 1 | 5 |

На сайте есть фильтр для выбора объявлений по параметрам

public function getAllAds(Request $request){
            $query = Kvartira::query();

           //Если применен фильтр
                  if($filter != 'Фильтр не применен'){

                 //Поиск по области
                 $query->when(isset($filter['oblast']) && $filter['oblast'] != null, function($query) 
                 use($filter){
                         return $query->where('oblast', $filter['oblast']);
                   });

                 //Поиск по городу
                 $query->when(isset($filter['gorod']) && $filter['gorod'] != null, function($query) 
                  use($filter){
                       return $query->where('gorod', $filter['gorod']);
                  });

После применения фильтра я получаю итоговые данные

$ads_arr = $query->where('control',  'Активно')
                ->orderBy('top', 'desc')
                ->orderBy('updated_at', 'desc')
                ->paginate(50);

Сортирую по 2 полям top , updated_at
Top - Если объявление подано в ТОП то получим вначале их,
и по полю created_at и получаю пагинацией по 50 записей.

Если в БД - 50 тысяч объявлений то при запросе Первой страницы
Результат возвращается через 2, 3 секунды.

А если запрос например объявлений со страницы 900 то = Результат 16, 17 секунд

Как уменьшить время получения результата на больших страницах?

Comments

[Константин Б.]

У вас очень простые запросы. Нужно добавить индексы и составные индексы на поля по которым ищите и сортируете

[Дмитрий]

Представьте себе массив из 50 тысяч элементов и попробуйте представить что вам надо отсортировать и выбрать 100 записей с 900 страницы. Как вам придется это сделать?

[Ипатьев]

На самом деле даже без индексов любые запросы с 50к записей - это меньше секунды.
Тут какие-то ужасы творятся в базе данных.

[lexstile]

isset($filter['oblast']) && $filter['oblast'] != null

Зачем 2 раза на null проверять?

[Ismail-1]

Константин Б., Ипатьев,

Добрый день!
Благодарю за ответ

Если выборка идет без orderBy запрос занимает менее 1 сек.

Я добавил индекс на поля top и updated_at по которым идет сортировка, но время не уменьшилось.

[Ипатьев]

добавлять отделные индексы бессмысленно, если сортировка идет по двум полям сразу
индекс должен быть один по двум полям.

[Ismail-1]

Ипатьев,
Да, 1 индекс на 2 поля добавлен

[Константин Б.]

Ismail-1, есть такая штука EXPLAIN, вам нужно его выполнить и проанализировать

[Ismail-1]

Константин Б.,
Я создал колонку ads_date с типом date
В БД создал рандомные записи, диапазон даты 1 месяц.

Добавил индекс на эту колонку

->orderBy('ads_date', 'desc')
            ->explain();
            return response()->json(['explain'=>$explain], 200);

Индекс не применяется

Extra: "Using where; Using filesort"
filtered: 10
id: 1
key: null
key_len: null
partitions: null
possible_keys: null
ref: null
rows: 61665
select_type: "SIMPLE"
table: "kvartiras"
type: "ALL"

Если делаю выборку например

->where('ads_date', '2023-04-01')
            ->explain();

Тогда индекс срабатывает

Extra: "Using where"
filtered: 10
id: 1
key: "ads_date"
key_len: "4"
partitions: null
possible_keys: "ads_date"
ref: "const"
rows: 2541
select_type: "SIMPLE"
table: "kvartiras"
type: "ref"