SELECT 
  cscart_products.*, 
  cscart_product_descriptions.*, 
  MIN(
    IF(
      cscart_product_prices.percentage_discount = 0, 
      cscart_product_prices.price, 
      cscart_product_prices.price - (
        cscart_product_prices.price * cscart_product_prices.percentage_discount
      )/ 100
    )
  ) as price, 
  GROUP_CONCAT(
    CASE WHEN (
      cscart_products_categories.link_type = 'M'
    ) THEN CONCAT(
      cscart_products_categories.category_id, 
      'M'
    ) ELSE cscart_products_categories.category_id END 
    ORDER BY 
      cscart_categories.storefront_id IN (0, 1) DESC, 
      (
        cscart_products_categories.link_type = 'M'
      ) DESC, 
      cscart_products_categories.category_position ASC, 
      cscart_products_categories.category_id ASC
  ) as category_ids, 
  popularity.total as popularity, 
  IF(
    shared_prices.product_id IS NOT NULL, 
    MIN(
      IF(
        shared_prices.percentage_discount = 0, 
        shared_prices.price, 
        shared_prices.price - (
          shared_prices.price * shared_prices.percentage_discount
        )/ 100
      )
    ), 
    MIN(
      IF(
        cscart_product_prices.percentage_discount = 0, 
        cscart_product_prices.price, 
        cscart_product_prices.price - (
          cscart_product_prices.price * cscart_product_prices.percentage_discount
        )/ 100
      )
    )
  ) as price, 
  companies.company as company_name, 
  cscart_product_sales.amount as sales_amount, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path, 
  MIN(point_prices.point_price) as point_price, 
  cscart_discussion.type as discussion_type, 
  cscart_product_review_prepared_data.average_rating average_rating, 
  cscart_product_review_prepared_data.reviews_count product_reviews_count, 
  categories.id_path as id_path 
FROM 
  cscart_products 
  LEFT JOIN cscart_product_prices ON cscart_product_prices.product_id = cscart_products.product_id 
  AND cscart_product_prices.lower_limit = 1 
  AND cscart_product_prices.usergroup_id IN (0, 0, 1) 
  LEFT JOIN cscart_product_descriptions ON cscart_product_descriptions.product_id = cscart_products.product_id 
  AND cscart_product_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_ult_product_prices shared_prices ON shared_prices.product_id = cscart_products.product_id 
  AND shared_prices.company_id = 1 
  AND shared_prices.lower_limit = 1 
  AND shared_prices.usergroup_id IN (0, 0, 1) 
  LEFT JOIN cscart_companies as companies ON companies.company_id = cscart_products.company_id 
  INNER JOIN cscart_products_categories ON cscart_products_categories.product_id = cscart_products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id 
  AND cscart_categories.storefront_id IN (0, 1) 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND (
    cscart_products.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_products.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_products.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
  AND cscart_products.status IN ('A', 'H') 
  LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = cscart_products.product_id 
  LEFT JOIN cscart_product_sales ON cscart_product_sales.product_id = cscart_products.product_id 
  AND cscart_product_sales.category_id = 224 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 278 
  AND cscart_seo_names.type = 'p' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
  AND cscart_seo_names.company_id = 1 
  LEFT JOIN cscart_product_point_prices as point_prices ON point_prices.product_id = cscart_products.product_id 
  AND point_prices.lower_limit = 1 
  AND point_prices.usergroup_id IN (0, 0, 1) 
  LEFT JOIN cscart_discussion ON cscart_discussion.object_id = cscart_products.product_id 
  AND cscart_discussion.object_type = 'P' 
  AND cscart_discussion.company_id = 1 
  LEFT JOIN cscart_product_review_prepared_data ON cscart_product_review_prepared_data.product_id = cscart_products.product_id 
  AND cscart_product_review_prepared_data.storefront_id = 0 
  INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = cscart_products.product_id 
  LEFT JOIN cscart_categories as categories ON categories.category_id = products_categories.category_id 
WHERE 
  cscart_products.product_id = 278 
  AND (
    companies.status IN ('A') 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.00115

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE point_prices system unique_key,src_k 0 const row not found
1 SIMPLE cscart_products const PRIMARY,status PRIMARY 3 const 1
1 SIMPLE popularity const PRIMARY,total PRIMARY 3 const 1
1 SIMPLE cscart_product_sales const PRIMARY,pa PRIMARY 6 const,const 1
1 SIMPLE cscart_discussion const object_id,idx_company_id object_id 10 const,const,const 0 unique row not found
1 SIMPLE cscart_product_review_prepared_data const PRIMARY PRIMARY 7 const,const 0 unique row not found
1 SIMPLE cscart_product_prices ref usergroup,product_id,lower_limit,usergroup_id product_id 3 const 1 Using where
1 SIMPLE cscart_product_descriptions const PRIMARY,product_id PRIMARY 9 const,const 1 Using where
1 SIMPLE shared_prices ref usergroup,product_id,company_id,lower_limit,usergroup_id product_id 3 const 1 Using where
1 SIMPLE companies eq_ref PRIMARY PRIMARY 4 const 1 Using where
1 SIMPLE cscart_products_categories ref PRIMARY,pt pt 3 const 1
1 SIMPLE cscart_categories eq_ref PRIMARY,c_status,p_category_id PRIMARY 3 cscart_dev_4_16.cscart_products_categories.category_id 1 Using where
1 SIMPLE cscart_seo_names const PRIMARY,dispatch PRIMARY 210 const,const,const,const,const 1 Using where
1 SIMPLE products_categories ref pt pt 3 const 1
1 SIMPLE categories eq_ref PRIMARY,p_category_id PRIMARY 3 cscart_dev_4_16.products_categories.category_id 1

Result

product_id product_code product_type status company_id list_price amount weight length width height shipping_freight low_avail_limit timestamp updated_timestamp usergroup_ids is_edp edp_shipping unlimited_download tracking free_shipping zero_price_action is_pbp is_op is_oper is_returnable return_period avail_since out_of_stock_actions localization min_qty max_qty qty_step list_qty_count tax_ids age_verification age_limit options_type exceptions_type details_layout shipping_params facebook_obj_type parent_product_id buy_now_url units_in_product show_price_per_x_units product_label_id product_label_id_by_admin lang_code product shortname short_description full_description meta_keywords meta_description search_words page_title age_warning_message promo_text unit_name price category_ids popularity company_name sales_amount seo_name seo_path point_price discussion_type average_rating product_reviews_count id_path
278 TSHIRT1 P A 1 75.00 82 0.000 0 0 0 0.00 0 1504253345 1697804132 0 N N N B N R N N N Y 10 0 N 0 0 0 0 N 0 bigpicture_template a:5:{s:16:"min_items_in_box";i:0;s:16:"max_items_in_box";i:0;s:10:"box_length";i:0;s:9:"box_width";i:0;s:10:"box_height";i:0;} 0 0.000 1.000 en T-shirt, Color: White 30.00000000 224M 5912 CS-Cart 8 t-shirt-color-white 223/224 223/224