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 (71) 
  AND companies.status IN ('A') 
  AND prices.price >= 503.49 
  AND prices.price <= 556.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.00716

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` <> 71)"
              }
            },
            {
              "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` >= 503.49) and (`atulecarter_atul_demo1`.`prices`.`price` <= 556.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	parent_product_ids	product_type	full_description
239	Apple iPhone 4S White	CS-Cart	509.99000000	239	P	0	P	<p> 16GB iPhone 4S locked AT&T model. Will work on AT&T. Now you can use your voice to use your iPhone. Just talk to Siri as you would to a person: "Do I need an umbrella?" or "Any great burgers around here?" or "Where's the closest ATM?" Siri not only understands what you say, it knows what you mean.1 It figures out the right apps to use to find the right answer. Then, just like a personal assistant, it answers you. Siri makes phone calls, sends messages, schedules meetings, sets reminders, and more. How much more? Just ask, and Siri tells you that, too. Two cores in the A5 chip deliver up to two times more power and up to seven times faster graphics.2 And you'll feel the effects. Fast. iPhone 4S is quick and responsive, which makes all the difference when you're launching apps, browsing the web, gaming, and doing just about everything. And no matter what you're doing, you can keep on doing it. Because the A5 chip is so power-efficient, iPhone 4S has outstanding battery life. This just might be the best camera ever on a phone. And with all-new optics, it just might be the only camera you need. Because behind every shot, you've got 8-megapixel resolution and a custom lens with a larger f/2.4 aperture. Not to mention an improved backside illumination sensor, excellent auto white balance, advanced color accuracy, face detection, and reduced motion blur. It all means that no matter how many people, how much light, and how much action you capture, everything looks exactly as it should. And just wait until you see the photos. Shoot stunning 1080p HD video everywhere you go. With all-new optics, the light is always right, the color is always vivid, and everything will look even better than you remember. Video stabilization steadies shaky shots. And you can edit video right on iPhone and share your lush life as fast as you shoot. </p>
167	D3100 + 18-55 VR Lens Kit	CS-Cart	549.95000000	167,167,167	P,P,P	0,0,0	P	<p> Beautiful pictures–Amazing movies–Incredibly Easy with the 14 Megapixel DX sensor and learn-as-you-grow Guide Mode. </p>
74	Droid 3	CS-Cart	524.99000000	74	P	0	P	<p>You're going places, and DROID 3 by Motorola can take you there. With incredible dual-core speed and brilliant qHD display. You're making a big Android smartphone statement. DROID 3 offers more power and greater responsiveness, and the efficient handling of a five-row keyboard. The thinnest full QWERTY smartphone. Ever.<br /><br />Key Features: <br />Adobe® Flash® Player for stellar video and gaming<br />Scratch-resistant Corning® Gorilla® Glass with glare reducing coating<br />View, capture and share on the large 4-inch qHD display<br />An 8MP camera with 1080p HD video and front-facing web cam to see and be seen<br />Video capture in 1080p HD; output to compatible device via HDMI® micro connector cable<br />Thinnest Full QWERTY smartphone ever.<br /> Super-charged dual-core 1GHz processor makes multi-tasking a snap<br /> What you get in the box: <br />DROID 3 phone <br />Battery Door <br />Battery <br />Charger <br />User Guide</p>