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 (158) 
  AND companies.status IN ('A') 
  AND prices.price >= 4512.50 
  AND prices.price <= 4987.50 
  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.00810

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` <> 158)"
              }
            },
            {
              "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` >= 4512.50) and (`atulecarter_atul_demo1`.`prices`.`price` <= 4987.50))"
              }
            },
            {
              "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
115	Evolve SST.2 X9 Complete Bike 10SPD '12	CS-Cart	4595.00000000	115	P	P	<p><span class="content">The all new 100mm travel Evolve sst.2 with ICT Technology is dialed in to maintain the legendary efficiency, handling and ride of a nimble full suspension cross country bike while harnessing the rolling efficiency and feel of the 29" wheel. The NEW super formed pivot integrated seat tube is lighter and stronger while the NEW semi-integrated tapered head tube is stiffer and more responsive. </span></p>
127	Fore CR5 SRAM Red	CS-Cart	4779.00000000	127	P	P	<p>An all new frame and new graphics package make the Fore CR5 look as fast as it really is. Now available with SRAM Red componants.</p> <p>What do you get when you interview hundreds of riders, questioning them about their desires for the ultimate road bike and then, spend thousands of hours translating those ideas into reality? A dream come true. We call it the Fezzari Foré CR5. It is the pinnacle of road bike performance. From its all new state of the art, proprietary Fezzari Racing Design Xr7 3K monocoque carbon frame to its best-of-market components, the Foré CR5 is THE ultimate road bike. Our designers and engineers didn't cut corners.<br /> <br /> Take, for instance, the state of the art proprietary Fezzari Racing Design Xr7 3K monocoque carbon fiber frame. We used the highest grade of carbon fiber available on the market. We made it even stiffer laterally by enlarging the bottom bracket shell and adding the BB30 bottom bracket/crank design, strengthening the headtube and making it more laterally stiff by enlarging the diameter to a 1.5" tapered design. We've also enlarged the seatstays near the rear axle for more lateral rigidity. We made the Xr7 frame even more vertically compliant for those rides on the cobblestones. We've done this by using an oval design in our top tube and down tube.  The new Xr7 frame is even more aerodynamic than before with the new F4 ultralight bladed carbon fork and internal cable routing on the top tube.  The Xr7 frame was recently tested to be one of the most durable frames on the market, and it weighs a mere 900 grams. Strength, lightweight, rigidity where you need it most, and flexible enough to take the edge off of rough roads -- it's the perfect frame for anyone who wants to get the most out of their riding experience.<br /> <br /> Next, take a look at the perfect blend of components. The Fore CR5 includes SRAM Red components shifters, brakes and derraileurs, FSA K-Force Light BB30 Carbon Crankset, Fezzari F4 XrT 3K carbon ultralite bladed carbon fork with carbon steerer tube, FSA K-Force compact carbon bars, FSA K- Force carbon seat post, and even carbon spacers on the headset.<br /> <br /> Finally, add the new sleek graphics and logos and you have--the ultimate road ride.<br /> <br /> You get the following upgrades over the CR3:</p> <ol> <li> <strong>Upgraded Frameset</strong> – from the Xr5 to the new Xr7 frame,  more lateral stiffness with a BB30 crank system, enlarged headtube for better cornering, and sprinting, added rigidity in the rear triangle, increased aerodynamics with the new F4 bladed fork design and internal cable routing.</li> <li> <strong>Upgraded Shifters</strong> – from Shimano Ultegra to the flagship SRAM Red, for smoother, more precise shifting</li> <li> <strong>Upgraded Front Derailleur</strong> – from Shimano Ultegra to SRAM Red, which is lighter and smoother</li> <li> <strong>Upgraded Cassette</strong> – from Shimano Ultegra to SRAM Red</li> <li> <strong>Upgraded Crankset</strong> – from FSA SLK Light to FSA K-Force Light, which is lighter</li> <li> <strong>Upgraded Chain</strong> – from Shimano Ultegra to SRAM Red</li> <li> <strong>Upgraded Wheelset</strong> – Mavic Ksyrium SL, which are lighter,stiffer, faster, and perform better</li> <li> <strong>Upgraded Brakes</strong> – from Shimano Ultegra to SRAM Red, which are lighter and smoother</li> <li> <strong>Upgraded Seatpost-  </strong>The  FSA K-force light seatpost is one of the lightest on the market.  It also completes the graphics package complimenting the crank and handle bar.</li> </ol> <p>When you get a Foré CR5, we custom fit it for you through our 23-Point Custom Setup program. We’ll get your specific  body measurements so we can outfit your bike to fit you perfectly.  You'll have the rest of the peloton envious of your ride.</p>
124	Truth SST.2 X9 Complete Bike 10SPD '12	CS-Cart	4595.00000000	124	P	P	<p><span class="content">The legendary performance of the 100mm travel Truth sst.2 has been enhanced through fine tuning and tweaking over countless seasons of XC and endurance racing in every condition imaginable. With Ellsworth's proprietary ICT suspension, SST tubing,a lighter and stronger pivot integrated seat tube and a semi-integrated tapered head tube - this is the fastest Truth ever created. </span></p>