How to optimize such a query?

Question

MySQL

The thing is that when executing such a request

( SELECT MAX ( discount_price ) FROM OPTIONS WHERE products.ID = OPTIONS.product_id ) AS max_price

very slow:

SELECT options.product_id,
  ( SELECT MAX ( discount_price ) FROM OPTIONS WHERE products.ID = OPTIONS.product_id ) AS max_price,
  products.slug,
  products.NAME AS NAME,
  products.ID AS ID,
  products.picture_id,
  meta_id,
  products.description AS description,
  updated_at,
  purchases,
  priority 
FROM
  "products"
  LEFT JOIN "options" ON "options"."product_id" = "products"."id" 
WHERE  "products"."product_status_id" = 1 
  AND "options"."active" = true
  AND "products"."deleted_at" IS NULL 
GROUP BY
  "products"."id",
  "options"."product_id" 
ORDER BY
  "max_price" ASC 
  LIMIT 24

Answer 1

Logically. Nested dependent subqueries are very bad. In general, the whole query is a curve and will not work in new versions of MySQL due to restrictions on fields when grouping.

SELECT *
  FROM `products` 
  JOIN (
    SELECT MAX(`discount_price`) AS `max_price`, `product_id`
      FROM `options`
      GROUP BY `product_id`
    ) AS `max` ON `max`.`product_id` = `products`.`id`
  ORDER BY `max`.`max_price` ASC
  LIMIT 24