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, 
  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, 
  products.master_product_offers_count, 
  products.master_product_id, 
  products.company_id, 
  descr1.full_description as full_description, 
  descr1.short_description as short_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_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) 
  LEFT JOIN cscart_master_products_storefront_offers_count AS master_products_storefront_offers_count ON master_products_storefront_offers_count.product_id = products.product_id 
  AND master_products_storefront_offers_count.storefront_id = 1 
WHERE 
  1 
  AND cscart_categories.category_id IN (177, 178, 179, 180, 181, 182) 
  AND (
    companies.status IN ('A') 
    OR products.company_id = 0
  ) 
  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 products.master_product_status IN ('A') 
  AND products.master_product_id = 0 
  AND (
    products.company_id > 0 
    OR (
      master_products_storefront_offers_count.count > 0
    )
  ) 
  AND products.product_type != 'D' 
GROUP BY 
  product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  0, 12

Query time 0.00267

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 275.0272348,
    "filesort": {
      "sort_key": "descr1.product, products.product_id",
      "temporary_table": {
        "filesort": {
          "sort_key": "case when products.parent_product_id <> 0 then products.parent_product_id else products.product_id end",
          "temporary_table": {
            "nested_loop": [
              {
                "table": {
                  "table_name": "cscart_categories",
                  "access_type": "range",
                  "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["category_id"],
                  "loops": 1,
                  "rows": 6,
                  "cost": 0.01122386,
                  "filtered": 100,
                  "attached_condition": "cscart_categories.category_id in (177,178,179,180,181,182) 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)"
                }
              },
              {
                "table": {
                  "table_name": "products_categories",
                  "access_type": "ref",
                  "possible_keys": ["PRIMARY", "pt"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["category_id"],
                  "ref": ["u985510652_ecartify_abhi.cscart_categories.category_id"],
                  "loops": 6,
                  "rows": 9027,
                  "cost": 8.88941862,
                  "filtered": 100
                }
              },
              {
                "table": {
                  "table_name": "products",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY",
                    "status",
                    "idx_master_product_id",
                    "wdbi_master_product_status_master_product_id_produc_46b93429beec",
                    "wdbi_master_product_id_master_product_status_produc_611007cab252",
                    "wdbi_master_product_id_master_product_status_produc_37d8c9a2112a",
                    "wdbi_master_product_id_master_product_status_status_1d4dcd83320a",
                    "wdbi_master_product_id_master_product_status_produc_909a0cf2d55b",
                    "wdbi_master_product_id_master_product_status_status_ff7ba2ae2fb0"
                  ],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["product_id"],
                  "ref": [
                    "u985510652_ecartify_abhi.products_categories.product_id"
                  ],
                  "loops": 54162,
                  "rows": 1,
                  "cost": 56.13367048,
                  "filtered": 100,
                  "attached_condition": "products.master_product_id = 0 and (products.usergroup_ids = '' or find_in_set(0,products.usergroup_ids) or find_in_set(1,products.usergroup_ids)) and products.`status` = 'A' and products.master_product_status = 'A' and products.product_type <> 'D'"
                }
              },
              {
                "table": {
                  "table_name": "companies",
                  "access_type": "eq_ref",
                  "possible_keys": ["PRIMARY"],
                  "key": "PRIMARY",
                  "key_length": "4",
                  "used_key_parts": ["company_id"],
                  "ref": ["u985510652_ecartify_abhi.products.company_id"],
                  "loops": 54162,
                  "rows": 1,
                  "cost": 48.44547848,
                  "filtered": 100,
                  "attached_condition": "trigcond(companies.`status` = 'A' or products.company_id = 0)"
                }
              },
              {
                "table": {
                  "table_name": "master_products_storefront_offers_count",
                  "access_type": "eq_ref",
                  "possible_keys": ["PRIMARY", "idx_storefront_id"],
                  "key": "PRIMARY",
                  "key_length": "8",
                  "used_key_parts": ["product_id", "storefront_id"],
                  "ref": [
                    "u985510652_ecartify_abhi.products_categories.product_id",
                    "const"
                  ],
                  "loops": 54162,
                  "rows": 1,
                  "cost": 48.44547848,
                  "filtered": 100,
                  "attached_condition": "trigcond(products.company_id > 0 or master_products_storefront_offers_count.count > 0) and trigcond(master_products_storefront_offers_count.product_id = products_categories.product_id)"
                }
              },
              {
                "table": {
                  "table_name": "descr1",
                  "access_type": "eq_ref",
                  "possible_keys": ["PRIMARY", "product_id"],
                  "key": "PRIMARY",
                  "key_length": "9",
                  "used_key_parts": ["product_id", "lang_code"],
                  "ref": [
                    "u985510652_ecartify_abhi.products_categories.product_id",
                    "const"
                  ],
                  "loops": 54162,
                  "rows": 1,
                  "cost": 59.76108808,
                  "filtered": 100,
                  "attached_condition": "trigcond(descr1.lang_code = 'en')"
                }
              },
              {
                "table": {
                  "table_name": "prices",
                  "access_type": "ref",
                  "possible_keys": [
                    "usergroup",
                    "product_id",
                    "lower_limit",
                    "usergroup_id",
                    "wdbi_product_id_lower_limit_usergroup_id_price_eff1727beeef",
                    "wdbi_usergroup_id_lower_limit_product_id_92b7e8f6a250",
                    "wdbi_product_id_lower_limit_usergroup_id_price_perc_3b2301d551d0",
                    "wdbi_lower_limit_usergroup_id_product_id_b93629431718"
                  ],
                  "key": "product_id",
                  "key_length": "3",
                  "used_key_parts": ["product_id"],
                  "ref": [
                    "u985510652_ecartify_abhi.products_categories.product_id"
                  ],
                  "loops": 54162,
                  "rows": 1,
                  "cost": 53.34087681,
                  "filtered": 100,
                  "attached_condition": "prices.lower_limit = 1 and prices.usergroup_id in (0,0,1)",
                  "using_index": true
                }
              }
            ]
          }
        }
      }
    }
  }
}

Result

product_id	product	company_name	product_ids	product_types	parent_product_ids	product_type	company_id	full_description
149	8GB E Series Walkman Video MP3	Parrot Products,Parrot Products	149	P	0	P	1	<p>Long lasting battery offering up to 50 hours of music or up to 10 hours of video playback, FM tuner, Karaoke mode, Clear Audio technologies, EX headphones, integrated mic and 2.0" LCD.</p>
242	Apple iPod Classic Black	Parrot Products,Parrot Products	242	P	0	P	1	<p>With 160GB of storage, iPod classic can hold up to 40,000 songs, 200 hours of video, or 25,000 photos.** That’s more than enough room for a day’s — or a lifetime’s — worth of entertainment.</p>
243	Apple iPod Classic White	Parrot Products,Parrot Products	243	P	0	P	1	<p>With 160GB of storage, iPod classic can hold up to 40,000 songs, 200 hours of video, or 25,000 photos.** That’s more than enough room for a day’s — or a lifetime’s — worth of entertainment.</p>
134	Creative ZEN Style M300 MP3 Player 16GB (Black)	Parrot Products,Parrot Products	134,134	P,P	0,0	P	1	<p>Don't let the compact appearance of the Creative ZEN Style M300 MP3 player fool you. For a media player this tiny, you’d be amazed at the array of features it can offer for a remarkably affordable price.</p>
135	Creative ZEN Touch 2 MP3 Player 8GB (Black)	Parrot Products,Parrot Products	135,135	P,P	0,0	P	1	<p>The ZEN line of portable media players has long been associated with the highest quality in audio playback, and the Creative ZEN Touch 2 continues to uphold this standard. But that's not all it offers. What makes this player truly stand out from the rest is that you can stream music wirelessly to compatible Bluetooth® headphones or speakers such as Creative's ZiiSound D5 and WP-300, and surf the Internet too.</p>
156	Extra Bass Headphones – 70mm	Parrot Products,Parrot Products	156	P	0	P	1	<p>Get that super bass. A tightly sealed acoustic design combines mega 70mm XB driver units – the world's largest1 – and king-sized cushion earpads to deliver ultra-deep bass.</p>
154	JR900 Wireless Headphones	Parrot Products,Parrot Products	154	P	0	P	1	<p>Koss JR900s are the music lover’s answer to true stereo and cordless freedom. Operating on one of the highest power bands available on the market, the Koss JR900 headphone system (consisting of a high quality, battery operated RF transmitter with AC adapter) offers the best in wireless audio technology, allowing you to roam freely over a 70,000-square-foot area. From room to room, inside to outside, even in bright sunlight, you can move to your heart’s content without the need for additional transmitters or worry of signal breakup.</p>
155	Porta Pro KTC On-Ear Headphone	Parrot Products,Parrot Products	155	P	0	P	1	<p>Koss Porta Pro KTC, the most sophisticated version of the Porta Pro, features a new technological upgrade which brings listeners closer to the music they love. Koss Touch Control (KTC) remote and microphone technology, made for iPhone®, iPad® and iPod®, gives listeners more control in fewer steps. Listeners are now able to stay connected, shuffle song and adjust volume with the flick of a switch.</p>
133	Samsung Galaxy Player 4.0	Parrot Products,Parrot Products	133,133	P,P	0,0	P	1	<p>Easily connect via Wi-Fi without any monthly service fees. The Samsung Galaxy Player can access the internet and apps anywhere there is an available hotspot. The sleek, lightweight 4.27 oz. design fits in any pocket and the brilliant 4” Super Clear LCD delivers incredible viewing quality.</p>
132	Samsung Galaxy Player 5.0	Parrot Products,Parrot Products	132,132	P,P	0,0	P	1	<p>Easily connect via Wi-Fi without any monthly service fees. The Samsung Galaxy Player can access the internet and apps anywhere there is an available hotspot. The sleek, lightweight 6.42 oz. design fits in any pocket and the brilliant 5” LCD delivers incredible viewing quality.</p>
146	Sansa Clip+ MP3 Player (Blue) - 4GB	Parrot Products,Parrot Products	146	P	0	P	1	<p>Small but powerful, the Sansa Clip+ is a tiny MP3 player that boasts an array of cool features, as well as distinctively big sound for its small size. Rock out with up to 1,000 songs on this blue player. &dagger; Enjoy even more play when you use the expandable memory slot to play preloaded microSDHC™ cards. slotRadio™ and slotMusic cards are preloaded with tunes ready to pop into the player’s memory card slot. Rock on with a 15-hour battery life. &dagger; &dagger;</p>
147	Sansa Clip+ MP3 Player (Red) - 4GB	Parrot Products,Parrot Products	147	P	0	P	1	<p>Small but powerful, the Sansa Clip+ is a tiny MP3 player that boasts an array of cool features, as well as distinctively big sound for its small size. Rock out with up to 1,000 songs on this red player. Enjoy even more play when you use the expandable memory slot to play preloaded microSDHC cards. slotRadio and slotMusic cards are preloaded with tunes ready to pop into the player’s memory card slot. Rock on with a 15-hour battery life.</p>