Sandbox

SELECT 
  tmall_products.*, 
  tmall_product_descriptions.*, 
  COALESCE(
    tmall_master_products_storefront_min_price.price, 
    MIN(
      IF(
        tmall_product_prices.percentage_discount = 0, 
        tmall_product_prices.price, 
        tmall_product_prices.price - (
          tmall_product_prices.price * tmall_product_prices.percentage_discount
        )/ 100
      )
    )
  ) as price, 
  GROUP_CONCAT(
    CASE WHEN (
      tmall_products_categories.link_type = 'M'
    ) THEN CONCAT(
      tmall_products_categories.category_id, 
      'M'
    ) ELSE tmall_products_categories.category_id END 
    ORDER BY 
      tmall_categories.storefront_id IN (0, 1) DESC, 
      (
        tmall_products_categories.link_type = 'M'
      ) DESC, 
      tmall_products_categories.category_position ASC, 
      tmall_products_categories.category_id ASC
  ) as category_ids, 
  popularity.total as popularity, 
  company_descr.i18n_company as company_name, 
  tmall_products.master_product_id, 
  tmall_products.master_product_status, 
  tmall_product_sales.amount as sales_amount, 
  tmall_discussion.type as discussion_type, 
  tmall_product_review_prepared_data.average_rating average_rating, 
  tmall_product_review_prepared_data.reviews_count product_reviews_count 
FROM 
  tmall_products 
  LEFT JOIN tmall_product_prices ON tmall_product_prices.product_id = tmall_products.product_id 
  AND tmall_product_prices.lower_limit = 1 
  AND tmall_product_prices.usergroup_id IN (0, 0, 1) 
  LEFT JOIN tmall_product_descriptions ON tmall_product_descriptions.product_id = tmall_products.product_id 
  AND tmall_product_descriptions.lang_code = 'en' 
  LEFT JOIN tmall_company_descriptions as company_descr ON company_descr.company_id = tmall_products.company_id 
  AND company_descr.lang_code = 'en' 
  LEFT JOIN tmall_companies as companies ON companies.company_id = tmall_products.company_id 
  INNER JOIN tmall_products_categories ON tmall_products_categories.product_id = tmall_products.product_id 
  INNER JOIN tmall_categories ON tmall_categories.category_id = tmall_products_categories.category_id 
  AND tmall_categories.storefront_id IN (0, 1) 
  AND (
    tmall_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, tmall_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, tmall_categories.usergroup_ids
    )
  ) 
  AND (
    tmall_products.usergroup_ids = '' 
    OR FIND_IN_SET(0, tmall_products.usergroup_ids) 
    OR FIND_IN_SET(1, tmall_products.usergroup_ids)
  ) 
  AND tmall_categories.status IN ('A', 'H') 
  AND tmall_products.status IN ('A', 'H') 
  LEFT JOIN tmall_product_popularity as popularity ON popularity.product_id = tmall_products.product_id 
  LEFT JOIN tmall_master_products_storefront_min_price ON tmall_master_products_storefront_min_price.product_id = tmall_products.product_id 
  AND tmall_master_products_storefront_min_price.storefront_id = 1 
  LEFT JOIN tmall_product_sales ON tmall_product_sales.product_id = tmall_products.product_id 
  AND tmall_product_sales.category_id = 236 
  LEFT JOIN tmall_discussion ON tmall_discussion.object_id = tmall_products.product_id 
  AND tmall_discussion.object_type = 'P' 
  LEFT JOIN tmall_product_review_prepared_data ON tmall_product_review_prepared_data.product_id = tmall_products.product_id 
  AND tmall_product_review_prepared_data.storefront_id = 0 
WHERE 
  tmall_products.product_id = 69 
  AND (
    companies.status IN ('A') 
    OR tmall_products.company_id = 0
  ) 
GROUP BY 
  tmall_products.product_id

Query time 0.00921

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.011784126,
    "nested_loop": [
      {
        "table": {
          "table_name": "tmall_master_products_storefront_min_price",
          "access_type": "system",
          "possible_keys": ["PRIMARY"],
          "rows": 0,
          "filtered": 0,
          "const_row_not_found": true
        }
      },
      {
        "table": {
          "table_name": "tmall_products",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "status"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "popularity",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "total"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["const"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "tmall_product_sales",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "pa"],
          "key": "PRIMARY",
          "key_length": "6",
          "used_key_parts": ["category_id", "product_id"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100,
          "unique_row_not_found": true
        }
      },
      {
        "table": {
          "table_name": "tmall_discussion",
          "access_type": "const",
          "possible_keys": ["object_id"],
          "key": "object_id",
          "key_length": "6",
          "used_key_parts": ["object_id", "object_type"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100,
          "unique_row_not_found": true
        }
      },
      {
        "table": {
          "table_name": "tmall_product_review_prepared_data",
          "access_type": "const",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "7",
          "used_key_parts": ["product_id", "storefront_id"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100,
          "unique_row_not_found": true
        }
      },
      {
        "table": {
          "table_name": "tmall_product_prices",
          "access_type": "ref",
          "possible_keys": [
            "usergroup",
            "product_id",
            "lower_limit",
            "usergroup_id"
          ],
          "key": "product_id",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.002024411,
          "filtered": 97.28681946,
          "attached_condition": "trigcond(tmall_product_prices.lower_limit = 1 and tmall_product_prices.usergroup_id in (0,0,1))"
        }
      },
      {
        "table": {
          "table_name": "tmall_product_descriptions",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "product_id"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["product_id", "lang_code"],
          "ref": ["const", "const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.001933826,
          "filtered": 100,
          "attached_condition": "trigcond(tmall_product_descriptions.lang_code = 'en')"
        }
      },
      {
        "table": {
          "table_name": "company_descr",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "10",
          "used_key_parts": ["company_id", "lang_code"],
          "ref": ["const", "const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.001933826,
          "filtered": 100,
          "attached_condition": "trigcond(company_descr.lang_code = 'en')"
        }
      },
      {
        "table": {
          "table_name": "companies",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["company_id"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.001933826,
          "filtered": 100,
          "attached_condition": "trigcond(companies.`status` = 'A')"
        }
      },
      {
        "table": {
          "table_name": "tmall_products_categories",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "pt"],
          "key": "pt",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.002024411,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "tmall_categories",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["category_id"],
          "ref": ["tmall.tmall_products_categories.category_id"],
          "loops": 1,
          "rows": 1,
          "cost": 0.001933826,
          "filtered": 100,
          "attached_condition": "tmall_categories.storefront_id in (0,1) and (tmall_categories.usergroup_ids = '' or find_in_set(0,tmall_categories.usergroup_ids) or find_in_set(1,tmall_categories.usergroup_ids)) and tmall_categories.`status` in ('A','H')"
        }
      }
    ]
  }
}

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	show_videos_before_images	autoplay_videos	parent_product_id	ab__stickers_manual_ids	ab__stickers_generated_ids	master_product_id	master_product_status	master_product_offers_count	lang_code	product	shortname	short_description	full_description	meta_keywords	meta_description	search_words	page_title	age_warning_message	promo_text	ab__emd_alternative_name	price	category_ids	popularity	company_name	sales_amount	discussion_type	average_rating	product_reviews_count
69	U0069R50SC	P	A	1	0.00	15	0.000	0	0	0	0.00	0	1328558400	1328684302	0	N	N	N		N		Y	N	N	Y	10	0	N						6	N	0			default	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;}	N	N	0			0	A	0	en	Rhyme			<p>HTC Rhyme™ and its suite of innovative accessories<br /> deliver seamless experiences.<br /><br />Docking Station<br />Effortlessly slide the HTC Rhyme into the Docking Station to charge it and automatically activate Dock Mode, turning your phone into an all-in-one alarm clock, music player, and picture frame.<br /><br /> Charm<br /> Attached to your bag, the Charm alerts you of calls, missed calls, and messages. It also keeps your phone within easy reach and goes with anything.<br /><br /> Tangle–free Headphones<br /> These headphones don’t just sound amazing—they’re virtually tangle-free, thanks to flat wires.<br /><br />Exclusive HTC Sense™ experience<br /> The HTC Rhyme delivers a visually refined and intuitive <br /> HTC Sense experience. Find what you want faster with streamlined menus across HTC Sense apps and get the live info you care about on the home screen with the Quick Launch widget. Stay on top of all your to-dos by creating tasks from emails and messages, then sync those tasks with your Microsoft® Exchange account for one to-do list on phone and PC.</p>						<p class="product-promo-text"><span class="product-promo-header">FREE US shipping over $100!</span><span class="product-promo-body">Orders within next 2 days will be shipped on Monday</span></p>		529.99000000	236M	15	CS-Cart