Коллеги добрый день. Достался запрос. Запрос выполняется 5 секунд mysql 5.4.

Основная проблема в:
GROUP BY и ORDER BY
Какие индексы нужно создать и помогут ли они?

Сам запрос
SELECT
products.product_id, products.url, products.category_id, products.brand_id, products.model, products.description, products.body, products.hit, products.show_menu, products.order_num, products.small_image, products.large_image, DATE_FORMAT(products.created_at, '%Y-%m-%d') as created, DATE_FORMAT(products.updated_at, '%Y-%m-%d') as modified, products.enabled, products.pricelist_comment as pricedescr, products.pre_order_days, products.rating, products.votes, products.header,
brands.name as brand, brands.url as brand_url,
categories.single_name as category, categories.name as category_name, categories.url as category_url, categories.image as category_image,
pv.price,
pv.variant_id,
pv.stock,
pv.sku,
pv.old_price,
pi.price as install_price,
pi.install_id as install_id,
pi.city_id as install_city_id,
pi.ot as install_ot,
pi.type as install_type,
media.name as media_image,
media.id as media_id
FROM products
LEFT JOIN categories ON categories.category_id = products.category_id
LEFT JOIN brands ON products.brand_id = brands.brand_id
LEFT JOIN products_categories ON products.product_id = products_categories.product_id
LEFT JOIN products_variants as pv ON products.product_id = pv.product_id
LEFT JOIN products_installs as pi ON products.product_id = pi.product_id
LEFT JOIN media ON products.product_id = model_id AND model_type = 'App\\Models\\Catalog\\Product' AND media.collection_name = 'main_image'
WHERE
categories.enabled = 1
AND products.enabled = 1
AND (pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL) AND
(pi.city_id = 0 OR pi.city_id = 0 OR pi.city_id IS NULL)
AND ( (categories.category_id in(149) ) OR (products_categories.category_id in(149) ) )
GROUP BY products.product_id
ORDER BY
CASE WHEN pv.stock = 0 AND products.pre_order_days = 0 THEN 1 ELSE 0 END,
CASE WHEN (pv.price = 0 OR pv.price IS NULL) AND pi.price = 0 THEN 1 ELSE 0 END, products.order_num
LIMIT 0, 24;

1. покажите индексы 2. вы не думали переехать на более новый mysql, т.к. 5.4 давно уже eol