Sandbox

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, 
  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 
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 = 209 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 409 
  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_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 
WHERE 
  cscart_products.product_id = 409 
  AND (
    companies.status IN ('A') 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.00144

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "shared_prices",
      "access_type": "system",
      "possible_keys": [
        "usergroup",
        "product_id",
        "company_id",
        "lower_limit",
        "usergroup_id"
      ],
      "rows": 0,
      "filtered": 0,
      "const_row_not_found": true
    },
    "table": {
      "table_name": "cscart_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": "companies",
      "access_type": "system",
      "possible_keys": ["PRIMARY"],
      "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": "cscart_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": 0,
      "filtered": 0,
      "unique_row_not_found": true
    },
    "table": {
      "table_name": "cscart_products_categories",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "pt",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "c_status"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["category_id"],
      "ref": ["herbaria_cscart_4_18.cscart_products_categories.category_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "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_categories.`status` in ('A','H')"
    },
    "block-nl-join": {
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": ["product_id"],
        "key": "product_id",
        "key_length": "9",
        "used_key_parts": ["product_id", "lower_limit", "usergroup_id"],
        "rows": 2,
        "filtered": 100
      },
      "buffer_type": "flat",
      "buffer_size": "256Kb",
      "join_type": "BNL",
      "attached_condition": "trigcond(1) and trigcond(cscart_product_prices.product_id = 409 and cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,0,1))"
    },
    "table": {
      "table_name": "cscart_product_descriptions",
      "access_type": "const",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "9",
      "used_key_parts": ["product_id", "lang_code"],
      "ref": ["const", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(cscart_product_descriptions.lang_code = 'en')"
    },
    "table": {
      "table_name": "cscart_seo_names",
      "access_type": "const",
      "possible_keys": ["PRIMARY", "dispatch"],
      "key": "PRIMARY",
      "key_length": "210",
      "used_key_parts": [
        "object_id",
        "type",
        "dispatch",
        "lang_code",
        "company_id"
      ],
      "ref": ["const", "const", "const", "const", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(cscart_seo_names.`type` = 'p' and cscart_seo_names.dispatch = '' and cscart_seo_names.lang_code = 'en')"
    },
    "table": {
      "table_name": "cscart_discussion",
      "access_type": "const",
      "possible_keys": ["object_id", "idx_company_id"],
      "key": "object_id",
      "key_length": "10",
      "used_key_parts": ["object_id", "object_type", "company_id"],
      "ref": ["const", "const", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(cscart_discussion.object_type = 'P')"
    },
    "table": {
      "table_name": "cscart_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
    }
  }
}

Result

product_id	product_code	product_type	status	list_price	amount	weight	length	width	height	shipping_freight	low_avail_limit	timestamp	is_edp	edp_shipping	unlimited_download	tracking	free_shipping	zero_price_action	is_pbp	is_op	is_oper	company_id	is_returnable	return_period	avail_since	localization	min_qty	max_qty	qty_step	list_qty_count	tax_ids	age_verification	age_limit	usergroup_ids	options_type	exceptions_type	details_layout	shipping_params	out_of_stock_actions	updated_timestamp	facebook_obj_type	parent_product_id	cp_bar	cp_seo_use_addon	cp_seo_no_index	cp_sr_variation_type	cp_fake_product	cp_not_crop_auto	lang_code	product	shortname	short_description	full_description	meta_keywords	meta_description	search_words	page_title	age_warning_message	promo_text	cp_contents	price	category_ids	popularity	company_name	sales_amount	seo_name	seo_path	discussion_type	average_rating	product_reviews_count
409	G-PF	P	A	1200.00	100	0.000	0	0	0	0.00	0	1615827737	N	N	N	D	N	R	N	N	N	1	Y	10	0		0	0	0	0	7,8,9	N	0	0	S	F	default	a:5:{s:16:"min_items_in_box";i:1;s:16:"max_items_in_box";i:4;s:10:"box_length";i:30;s:9:"box_width";i:20;s:10:"box_height";i:20;}	N	1743782812	activity	0	0.000	Y	D	M	N	N	en	Create Your Own Soap Variety		Would you like to compose a custom soap variety for your special event? You could make the batch yourself under the guidance of a master soap maker and create something personal.	<p>Would you like to compose a custom soap variety for your special event? You could make the batch yourself under the guidance of a master soap maker and create something personal. </p> <p><img src="https://herbariasoap.com/images/companies/1/Oatmeal475-80.jpg?1616434212392" alt="" width="475" height="444" /> </p> <p><strong>Step 1 </strong> Work with us to come up with your personal variety. Select the essential oils, emollients, texture, and color. In a sit-down meeting in our store, we'll go over your preferences and choose ingredients that will help tell your story. You're welcome to choose a soap we already create. Some essential oils, such as sandalwood or frankincense, are too costly to use at the standard price and will add to the cost.</p> <p><img src="https://herbariasoap.com/images/companies/1/FrenchLogs475-80.jpg?1616434500042" alt="" width="475" height="475" /></p> <p><strong>Step 2</strong> In a separate meeting with our soaper, Blake Larson, you will create your soap together. You can be as hands-on or hands-free as you like. All safety precautions will be followed. Photographs or videos of your work can be taken. </p> <p><strong><img src="https://herbariasoap.com/images/companies/1/front2014-600-80.jpg?1584112386923" alt="Use your photo" width="475" height="406" /></strong></p> <p><strong>Step 3</strong> Decide the size and custom wrapping of your personalized soap. Your creation will come out as one large block of soap for us to cut into whatever sizes fit your occasion. Your soap block could be cut into 165 full-size bars, 330 half-size bars, or 900 cutie-sized bars. Combinations of those sizes can also be made.</p> <p>Custom wrapping for your soap will be done by our in-house designer, Melissa Gibbs, where you can be shown several designs and help guide the creative process. </p> <p><strong>Step 4</strong> As part of making cold-process soap, your creation will need to dry for 3 full weeks. After that, we will need to apply your custom wrapper to each soap. We ask for a full month from the date the soap is made for pickup.</p> <p><strong>Step 5</strong> Enjoy. As you will have worked with our soaper to create your personalized soap, your recipe will be kept on file. If the want ever arises for more of your personal soap, it can be made again at a discounted rate. </p> <p><img src="https://herbariasoap.com/images/companies/1/back2014-2-600-80.jpg?1584111911100" alt="Back of Custom Favor" width="600" height="510" /></p> <p><em>We identify Herbaria and its all-natural ingredients on the back of each wrapper.</em></p> <p>Please feel free to call us at 314-601-3904 or stop by our store at 2016 Marconi Ave. Saint Louis, MO 63121, to go over any questions or details before purchasing.</p>	Custom soap gifts	Create your own soap masterpiece.		All natural handmade soaps by Herbaria				1200.00000000	209M	31903	Herbaria		create-your-own-soap-variety.html	2/10/209