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 
  INNER JOIN cscart_product_features_values as c_var ON c_var.product_id = products.product_id 
  AND c_var.lang_code = 'en' 
  AND c_var.variant_id = 130 
  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 (
    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.00266

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.21061038,
    "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": "c_var",
                  "access_type": "ref",
                  "possible_keys": [
                    "variant_id",
                    "lang_code",
                    "product_id",
                    "idx_product_feature_variant_id"
                  ],
                  "key": "variant_id",
                  "key_length": "3",
                  "used_key_parts": ["variant_id"],
                  "ref": ["const"],
                  "loops": 1,
                  "rows": 21,
                  "cost": 0.004726305,
                  "filtered": 54.89857483,
                  "attached_condition": "c_var.lang_code = 'en'",
                  "using_index": true
                }
              },
              {
                "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.c_var.product_id"],
                  "loops": 21,
                  "rows": 1,
                  "cost": 0.03598644,
                  "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": 21,
                  "rows": 1,
                  "cost": 0.01960244,
                  "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.c_var.product_id", "const"],
                  "loops": 21,
                  "rows": 1,
                  "cost": 0.01960244,
                  "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 = c_var.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.c_var.product_id", "const"],
                  "loops": 21,
                  "rows": 1,
                  "cost": 0.03598644,
                  "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": "usergroup",
                  "key_length": "3",
                  "used_key_parts": ["product_id"],
                  "ref": ["u985510652_ecartify_abhi.c_var.product_id"],
                  "loops": 21,
                  "rows": 1,
                  "cost": 0.03745917,
                  "filtered": 100,
                  "attached_condition": "prices.lower_limit = 1 and prices.usergroup_id in (0,0,1)"
                }
              },
              {
                "table": {
                  "table_name": "products_categories",
                  "access_type": "ref",
                  "possible_keys": ["PRIMARY", "pt"],
                  "key": "pt",
                  "key_length": "3",
                  "used_key_parts": ["product_id"],
                  "ref": ["u985510652_ecartify_abhi.c_var.product_id"],
                  "loops": 21,
                  "rows": 1,
                  "cost": 0.037644705,
                  "filtered": 100,
                  "using_index": true
                }
              },
              {
                "table": {
                  "table_name": "cscart_categories",
                  "access_type": "eq_ref",
                  "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
                  "key": "PRIMARY",
                  "key_length": "3",
                  "used_key_parts": ["category_id"],
                  "ref": [
                    "u985510652_ecartify_abhi.products_categories.category_id"
                  ],
                  "loops": 21,
                  "rows": 1,
                  "cost": 0.01960244,
                  "filtered": 100,
                  "attached_condition": "(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)"
                }
              }
            ]
          }
        }
      }
    }
  }
}

Result

product_id	product	company_name	product_ids	product_types	parent_product_ids	product_type	company_id	full_description
170	588 Chrome Wedge	Parrot Products,Parrot Products	170	P	0	P	1	<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>
177	CG BLACK DRIVER	Parrot Products,Parrot Products	177	P	0	P	1	<p>With a sleek and striking look, the CG Black driver weighs only 265 grams, making it the lightest driver on the market to help players generate measurable increases in swing speed and distance with the same swing effort. The advanced aerodynamics found in the driver’s Speed Crown Design – an innovative, teardrop-inspired crown profile – maximize distance by reducing drag at all angles of airflow during the swing. MaxCOR2 Variable Face Thickness Technology reduces face weight and increases trampoline effect on off-center hits, meaning higher ball speed and more distance on off-center hits. Plus, a high-density weight screw at the rear of the sole plate positions the center of gravity lower and deeper to promote a high, draw-biased trajectory with optimal launch conditions for effortless distance. The CG Black driver features a Miyazaki C. Kua 39 Limited Edition graphite shaft and a Golf Pride Ultralite grip, a combination that decreases overall club weight for faster swing speed.</p>
186	CG Black Staff Bag	Parrot Products,Parrot Products	186	P	0	P	1	<h3 class="product_availability">Item is not available at this time.<br />Can be Preordered.</h3>
185	CG Lightweight Cart Bag	Parrot Products,Parrot Products	185	P	0	P	1	<p>CG Lightweight Cart bags are perfect for push and power carts</p>
179	CG16 Black Pearl Wedge	Parrot Products,Parrot Products	179	P	0	P	1	<div class="overviewbody"> <p>Based on the shape of the tour proven CG14<span style="font-size: 8pt;">™</span> wedge, the CG16<span style="font-size: 8pt;">™</span> has a hint of offset and is slightly larger for added forgiveness.  The face features the same spin maximizing technology featured on the CG15<span style="font-size: 8pt;">™</span> wedge – Tour Zip Grooves<span style="font-size: 8pt;">™</span> in combination with Cleveland<span style="font-size: 8pt;">®</span> Golf’s breakthrough Laser Milled<span style="font-size: 8pt;">™ </span>surface roughness technology.  Using the precision accuracy of a laser, each face features four perfectly calibrated texture lines milled between each groove exactly to the conforming roughness limit.  This surface roughness application optimizes ball-to-face friction and maximizes spin within the Rules of Golf.</p> </div>
786975	White Elegant lighting monaco 8 light chandelier	AutoZone	786975,786975,786975	P,P,P	0,0,0	P	17	<p>The Elegant Lighting Monaco 8-Light Chandelier is a stunning statement lighting fixture designed to bring sophistication, warmth, and a luxurious touch to any interior. Featuring eight beautifully arranged lights, this chandelier provides ample illumination while creating an elegant focal point in dining rooms, living areas, entryways, bedrooms, and other stylish spaces.</p> <p>Its refined design combines classic elegance with a contemporary aesthetic, making it easy to complement a wide range of interior styles. The carefully crafted frame and balanced eight-light configuration add visual appeal while ensuring light is distributed beautifully throughout the room. Whether you are refreshing your home décor or creating an upscale atmosphere, the Monaco 8-Light Chandelier adds a distinctive decorative element to your space.</p> <p>Perfect for both everyday lighting and special occasions, this chandelier can enhance the ambiance of your home with its graceful presence and inviting illumination. Its sophisticated appearance makes it an excellent choice for homeowners looking to create a polished, comfortable, and luxurious environment.</p>
181	Women's Bloom Complete Set	Parrot Products,Parrot Products	181	P	0	P	1	<p>Created for women golfers looking to improve their game, the Bloom 14 Piece set covers all of her equipment needs. Designed specifically for her swing, the 100% titanium Driver and Stainless Steel Fairway woods are designed with today’s modern lightweight and oversized technology to drastically increase distance and consistency-particularly on mishits. The hybrid long irons are hollow and extremely forgiving and are packaged together with the short game simplifying NIBLICK – to make the game even easier to learn. If that isn’t enough, the classically designed mini boot bag has retro details yet modern convenience with ample pocket space and detachable wristlet.</p>