Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS (
    CASE WHEN products.parent_product_id <> 0 THEN products.parent_product_id ELSE products.product_id END
  ) AS product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  MIN(
    IF(
      prices.percentage_discount = 0, 
      prices.price, 
      prices.price - (
        prices.price * prices.percentage_discount
      )/ 100
    )
  ) as price, 
  GROUP_CONCAT(
    products.product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_ids, 
  GROUP_CONCAT(
    products.product_type 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_types, 
  GROUP_CONCAT(
    products.parent_product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS parent_product_ids, 
  products.product_type, 
  products.parent_product_id, 
  descr1.full_description as full_description 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'en' 
  LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  LEFT JOIN cscart_product_prices as prices_2 ON prices.product_id = prices_2.product_id 
  AND prices_2.lower_limit = 1 
  AND prices_2.price < prices.price 
  AND prices_2.usergroup_id IN (0, 0, 1) 
  LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id 
  INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id 
  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') 
  AND cscart_categories.storefront_id IN (0, 1) 
WHERE 
  1 
  AND products.product_id NOT IN (205) 
  AND companies.status IN ('A') 
  AND prices.price >= 142.49 
  AND prices.price <= 157.49 
  AND (
    products.usergroup_ids = '' 
    OR FIND_IN_SET(0, products.usergroup_ids) 
    OR FIND_IN_SET(1, products.usergroup_ids)
  ) 
  AND products.status IN ('A') 
  AND prices.usergroup_id IN (0, 0, 1) 
  AND prices_2.price IS NULL 
  AND products.company_id IN('1', '2', '3', '4', '5', '6') 
  AND products.product_type != 'D' 
GROUP BY 
  product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  0, 3

Query time 0.00762

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "44.30"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "buffer_result": {
          "using_temporary_table": true,
          "nested_loop": [
            {
              "table": {
                "table_name": "companies",
                "access_type": "ALL",
                "possible_keys": [
                  "PRIMARY"
                ],
                "rows_examined_per_scan": 6,
                "rows_produced_per_join": 1,
                "filtered": "16.67",
                "cost_info": {
                  "read_cost": "3.27",
                  "eval_cost": "0.20",
                  "prefix_cost": "3.47",
                  "data_read_per_join": "7K"
                },
                "used_columns": [
                  "company_id",
                  "status",
                  "company"
                ],
                "attached_condition": "((`atulecarter_atul_demo1`.`companies`.`status` = 'A') and (`atulecarter_atul_demo1`.`companies`.`company_id` in ('1','2','3','4','5','6')))"
              }
            },
            {
              "table": {
                "table_name": "cscart_categories",
                "access_type": "ALL",
                "possible_keys": [
                  "PRIMARY",
                  "c_status",
                  "p_category_id"
                ],
                "rows_examined_per_scan": 86,
                "rows_produced_per_join": 3,
                "filtered": "4.00",
                "using_join_buffer": "Block Nested Loop",
                "cost_info": {
                  "read_cost": "20.01",
                  "eval_cost": "0.69",
                  "prefix_cost": "24.16",
                  "data_read_per_join": "8K"
                },
                "used_columns": [
                  "category_id",
                  "storefront_id",
                  "usergroup_ids",
                  "status"
                ],
                "attached_condition": "(((`atulecarter_atul_demo1`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`atulecarter_atul_demo1`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`atulecarter_atul_demo1`.`cscart_categories`.`usergroup_ids`)) and (`atulecarter_atul_demo1`.`cscart_categories`.`status` in ('A','H')) and (`atulecarter_atul_demo1`.`cscart_categories`.`storefront_id` in (0,1)))"
              }
            },
            {
              "table": {
                "table_name": "products_categories",
                "access_type": "ref",
                "possible_keys": [
                  "PRIMARY",
                  "pt"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "category_id"
                ],
                "key_length": "3",
                "ref": [
                  "atulecarter_atul_demo1.cscart_categories.category_id"
                ],
                "rows_examined_per_scan": 3,
                "rows_produced_per_join": 10,
                "filtered": "100.00",
                "using_index": true,
                "cost_info": {
                  "read_cost": "3.63",
                  "eval_cost": "2.06",
                  "prefix_cost": "29.85",
                  "data_read_per_join": "165"
                },
                "used_columns": [
                  "product_id",
                  "category_id"
                ],
                "attached_condition": "(`atulecarter_atul_demo1`.`products_categories`.`product_id` <> 205)"
              }
            },
            {
              "table": {
                "table_name": "products",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "status"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "atulecarter_atul_demo1.products_categories.product_id"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 0,
                "filtered": "5.00",
                "cost_info": {
                  "read_cost": "10.32",
                  "eval_cost": "0.10",
                  "prefix_cost": "42.24",
                  "data_read_per_join": "2K"
                },
                "used_columns": [
                  "product_id",
                  "product_type",
                  "status",
                  "company_id",
                  "usergroup_ids",
                  "parent_product_id"
                ],
                "attached_condition": "((`atulecarter_atul_demo1`.`products`.`company_id` = `atulecarter_atul_demo1`.`companies`.`company_id`) and ((`atulecarter_atul_demo1`.`products`.`usergroup_ids` = '') or find_in_set(0,`atulecarter_atul_demo1`.`products`.`usergroup_ids`) or find_in_set(1,`atulecarter_atul_demo1`.`products`.`usergroup_ids`)) and (`atulecarter_atul_demo1`.`products`.`status` = 'A') and (`atulecarter_atul_demo1`.`products`.`product_type` <> 'D'))"
              }
            },
            {
              "table": {
                "table_name": "prices",
                "access_type": "ref",
                "possible_keys": [
                  "usergroup",
                  "product_id",
                  "lower_limit",
                  "usergroup_id"
                ],
                "key": "usergroup",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "atulecarter_atul_demo1.products_categories.product_id"
                ],
                "rows_examined_per_scan": 3,
                "rows_produced_per_join": 0,
                "filtered": "3.26",
                "index_condition": "((`atulecarter_atul_demo1`.`prices`.`lower_limit` = 1) and (`atulecarter_atul_demo1`.`prices`.`usergroup_id` in (0,0,1)))",
                "cost_info": {
                  "read_cost": "1.56",
                  "eval_cost": "0.01",
                  "prefix_cost": "44.10",
                  "data_read_per_join": "1"
                },
                "used_columns": [
                  "product_id",
                  "price",
                  "percentage_discount",
                  "lower_limit",
                  "usergroup_id"
                ],
                "attached_condition": "((`atulecarter_atul_demo1`.`prices`.`price` >= 142.49) and (`atulecarter_atul_demo1`.`prices`.`price` <= 157.49))"
              }
            },
            {
              "table": {
                "table_name": "prices_2",
                "access_type": "ref",
                "possible_keys": [
                  "usergroup",
                  "product_id",
                  "lower_limit",
                  "usergroup_id"
                ],
                "key": "usergroup",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "atulecarter_atul_demo1.products_categories.product_id"
                ],
                "rows_examined_per_scan": 3,
                "rows_produced_per_join": 0,
                "filtered": "9.74",
                "not_exists": true,
                "cost_info": {
                  "read_cost": "0.15",
                  "eval_cost": "0.00",
                  "prefix_cost": "44.29",
                  "data_read_per_join": "0"
                },
                "used_columns": [
                  "product_id",
                  "price",
                  "lower_limit",
                  "usergroup_id"
                ],
                "attached_condition": "(<if>(found_match(prices_2), isnull(`atulecarter_atul_demo1`.`prices_2`.`price`), true) and <if>(is_not_null_compl(prices_2), ((`atulecarter_atul_demo1`.`prices_2`.`lower_limit` = 1) and (`atulecarter_atul_demo1`.`prices_2`.`price` < `atulecarter_atul_demo1`.`prices`.`price`) and (`atulecarter_atul_demo1`.`prices_2`.`usergroup_id` in (0,0,1))), true))"
              }
            },
            {
              "table": {
                "table_name": "descr1",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "product_id"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "product_id",
                  "lang_code"
                ],
                "key_length": "9",
                "ref": [
                  "atulecarter_atul_demo1.products_categories.product_id",
                  "const"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 0,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "0.01",
                  "eval_cost": "0.00",
                  "prefix_cost": "44.30",
                  "data_read_per_join": "68"
                },
                "used_columns": [
                  "product_id",
                  "lang_code",
                  "product",
                  "full_description"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Result

product_id	product	company_name	price	product_ids	product_types	product_type	full_description
170	588 Chrome Wedge	CS-Cart	145.99000000	170	P	P	<p>The 588 Forged wedge maintains the popular, tour-proven shape of its predecessor with a series of technological breakthroughs to provide even more performance. Every wedge is Precision Forged from 1025 carbon steel for incredibly soft, solid feel and the highest level of manufacturing consistency for supreme confidence and trust over every shot. Tour Zip Grooves™ and Laser Milled™ technology maximize spin in accordance with USGA regulations. Exclusive to the 588 Forged wedge is the Tour Concept Wedge steel shaft, co-developed and engineered by Cleveland Golf and industry leader True Temper. With a unique stiffness profile that features a less active tip, this shaft promotes a more penetrating ball flight, increased spin and optimal distance control along with superior feel. Choose from two different finishes – Satin and Chrome – as well as low, standard and high bounce options.</p>
15	Nike Chambray Super Runner Men's Jacket	ACME Corp	150.00000000	15	P	P	<div class="text description black-white"> <div class="product-description-component"> <p><strong>The Nike Chambray Super Runner Jacket: A durable, updated classic </strong></p> <p><br></p> <p>The timeless Windrunner design gets a premium upgrade in the Nike Chambray Super Runner Men's Jacket. Created with tough, water-resistant fabric and a mesh lining, this jacket combines breathability and protection from the elements.</p> <p><br></p> <p><strong>Benefits</strong></p> <li>Hood with drawcord for an adjustable fit</li> <li>Overlay at yoke and raglan sleeves for style and enhanced protection</li> <li>Rib cuffs and hem for a fit that stays in place</li> <li>Mesh lining for enhanced ventilation</li> <li>Durable, wind-resistant fabric to protect against the elements</li> <p><br></p> <p><strong>Additional Details</strong></p> <li>Side zip pockets for secure storage</li> <li>Fabric: Body: 100% cotton. Lining and overlay: 100% polyester.</li> <li>Machine wash</li> <li>Imported</li> </div> </div>
125	Samsung Galaxy S II	CS-Cart	149.99000000	125	P	P	<dl><dt>The Galaxy S II has an amazing 4.27" Super AMOLED Plus screen. This superb display has razor sharp clarity and breathtaking color. See every detail of videos and pictures. See the full range of hues in all their subtlety and brilliance. And with its superior wide viewing angle, you don't have to be directly in front of the screen to see a perfect image. Truth is, you really have to see it to believe it.</dt></dl>