Как сделать индекс для запроса с group by и order by?
Сам запрос такой
SELECT Ad.id, Ad.title, Ad.description, Ad.posting_date, Ad.is_colored_listing, Ad.bg_color, Ad.fg_color, `Adi`.`image_name`, `Adc`.`category_alias`
FROM `daype_ads` AS `Ad`
LEFT JOIN daype_categories AS `Adc` ON ( `Ad`.`category_id` = `Adc`.`id` )
LEFT JOIN daype_images AS `Adi` ON ( `Ad`.`id` = `Adi`.`ad_id` )
WHERE `city_id` =25
AND `Ad`.`category` =4
AND `Ad`.`status` =1
AND `category_id` =33
GROUP BY `Ad`.`id`
ORDER BY `posting_date` DESC
LIMIT 20, 20
Упрощу запрос до такого
EXPLAIN SELECT Ad.id, Ad.title, Ad.description, Ad.posting_date, Ad.is_colored_listing, Ad.bg_color, Ad.fg_color
FROM `daype_ads` AS `Ad`
WHERE `city_id` =25
AND `Ad`.`category` =4
AND `Ad`.`status` =1
AND `category_id` =33
GROUP BY `Ad`.`id`
ORDER BY `posting_date` DESC
LIMIT 20, 20
explain говорит что использует индекс по category_id и составной по city_id++ category+status+category_id
но все равно работает не очень быстро.
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Ad index_merge category_id,category,city_id,citycategory,forcateg… category_id,forcategory 5,19 NULL 2538 Using intersect(category_id,forcategory); Using wh…
Значит что category равно четырем, а category_id равно 33.
Если вас смущает 'Ad'.
это сверху есть FROM `daype_ads` AS `Ad`
Вполне возможно что я не понял ваш вопрос и ответил чепуху.
Вопрос был почему два айдишника с похожими названиями хранятся в одной таблице? Что это вообще за SQL, какие реальные данные он выбирает? Может статься что у вас хранится всё не так как вам нужно.
Благодарю за разъяснение. Ну и попутно вопрос, почему в одном условии использован алиас, а в другом нет. (но это оффтоп)
Вообще хотелось бы видеть структуру таблицы и индексов на ней.
Вообще вряд ли это нормально — категория и подкатегория в этой таблице (с остальными данными). Подкатегории должны быть в отдельной таблице, один-ко-многим к таблице категорий. Скорее всего у вас нарушение 3НФ, из-за этого избыток в индексах и медленная работа. Ну да сложно сказать пока не понятно схему этих таблиц и описание полей.
Возможно я сильно не прав, но вполне можно обстрагироваться от этих понятий, и считать что там хранится два столбца с целыми числами и индекс сделать полностью покрывающий не получается не из за них, а из за group by и order by.
Про использован и не использован просто опечатка программиста, не более того.
Для начала попробуйте максимально упростить запрос.
Например, как уже сказали ниже — GROUP BY `Ad`.`id` тут никуда не упирается — убрать.
Заодно убрать окно(limit) и сортировку и выполнить (узнать время выполнения), заэксплейнить. Далее, возвращая убранные куски узнать где собака зарыта.
Какова селективность составного индекса? Число элементов от общего, может оптимизатор его не использует.
Может еще posting_date добавить в индекс? Лимит тоже как бы фильтрует, но уже по дате.
Как посмотреть селективность составного индекса?
Есть индекс составной city_id++ category+status+category_id+posting_date
есть отдельный по posting_date
Средний
