Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  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_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 cscart_categories.category_id IN (223, 224, 225, 226, 227) 
  AND companies.status IN ('A') 
  AND products.company_id = 1 
  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.company_id = 1 
  AND products.parent_product_id = 0 
  AND products.company_id IN('1', '2', '3', '4', '5', '6') 
  AND products.product_type != 'D' 
GROUP BY 
  products.product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  0, 12

Query time 0.00120

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.21"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "companies",
              "access_type": "const",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "company_id"
              ],
              "key_length": "4",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.20",
                "prefix_cost": "0.00",
                "data_read_per_join": "7K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "range",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 0,
              "filtered": "4.00",
              "index_condition": "(`atulecarter_atul_demo1`.`cscart_categories`.`category_id` in (223,224,225,226,227))",
              "cost_info": {
                "read_cost": "11.97",
                "eval_cost": "0.04",
                "prefix_cost": "12.01",
                "data_read_per_join": "534"
              },
              "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": 0,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.21",
                "eval_cost": "0.12",
                "prefix_cost": "12.34",
                "data_read_per_join": "9"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "products",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "status",
                "idx_parent_product_id"
              ],
              "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": "8.52",
              "cost_info": {
                "read_cost": "0.60",
                "eval_cost": "0.01",
                "prefix_cost": "13.06",
                "data_read_per_join": "250"
              },
              "used_columns": [
                "product_id",
                "product_type",
                "status",
                "company_id",
                "usergroup_ids",
                "parent_product_id"
              ],
              "attached_condition": "((`atulecarter_atul_demo1`.`products`.`parent_product_id` = 0) and (`atulecarter_atul_demo1`.`products`.`company_id` = 1) 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": "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.05",
                "eval_cost": "0.01",
                "prefix_cost": "13.12",
                "data_read_per_join": "238"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product",
                "full_description"
              ]
            }
          },
          {
            "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": "97.36",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.05",
                "eval_cost": "0.03",
                "prefix_cost": "13.21",
                "data_read_per_join": "3"
              },
              "used_columns": [
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "attached_condition": "((`atulecarter_atul_demo1`.`prices`.`lower_limit` = 1) and (`atulecarter_atul_demo1`.`prices`.`usergroup_id` in (0,0,1)))"
            }
          }
        ]
      }
    }
  }
}

Result

product_id	product	company_name	product_type	full_description
12	100g Pants	CS-Cart	P	<p> When coach calls you off the bench, you need warm-up pants that come off in three seconds or less. That’s why these men's adidas 100g basketball pants have tear-away snaps down the sides, so you're ready for action as fast as a superhero. </p>
325	100g Pants	CS-Cart	P	<p> When coach calls you off the bench, you need warm-up pants that come off in three seconds or less. That’s why these men's adidas 100g basketball pants have tear-away snaps down the sides, so you're ready for action as fast as a superhero. </p>
78	adizero Rush Shoes	CS-Cart	P	<p>Get moving in the lightweight adizero™ Rush shoes by adidas. These featherweight, dynamic men's running shoes are just 7.5 ounces of high-mileage style, with adiPRENE®+ support in the forefoot, a light synthetic upper and a thin rubber outsole for a close</p>
79	adiZero Tempaia Shoes	CS-Cart	P	<p> The adiZero™ Tempaia shoes by adidas are lightweight, breathable tennis shoes designed for multi-court use. They feature a breathable upper, adiPRENE®+ in the forefoot for propulsion, adiPRENE® heel cushioning and EXTENDED TORSION® for support. </p>
11	Collegiate 72 Tee	CS-Cart	P	<p>It's not how old you are, it's how stylish, and this men's adidas Originals Collegiate 72 Tee shirt feels just as fresh as it was the day the Trefoil was born in 1972. That's why this t-shirt features a collegiate "72" on the front.</p>
41	Gift Set (Onesie Plus Socks)	CS-Cart	P	<p>Welcome the new addition with this adidas Originals Gift Set, featuring a one-piece romper and matching socks. The romper has a sleepy-time Trefoil character on the back, while the front bristles with fun in vibrant stripes.</p> <div class="prodDetail"> <h3>Details</h3> <ul> <li>Crewneck</li> <li>Full snap-button front for easy on and off</li> <li>Allover yarn-dyed stripe pattern on front</li> <li>Solid-color back with Trefoil-inspired graphic</li> <li>Matching socks included</li> <li>95% cotton / 5% elastane single jersey</li> <li>Imported</li> </ul> </div>
81	Minnetonka Original Cowhide Driving Moc	CS-Cart	P	<div class="linePadding leadingEquity">Authentic designs. Free-spirited heritage. Shaping the moccasin's place in history since 1946.</div> <div class="linePadding">Leather</div> <ul class="equityBullets firstSet"> <li>• Genuine leather construction </li> <li>• Lace-up front </li> <li>• Long-wearing nub bottom </li> <li>• Cushioned leather insole </li> <li>• Contrast stitching </li> </ul>
107	Montblanc Classic Collection Money Clip	CS-Cart	P	<p>Platinum-plated money clip with black onyx inlay</p>
109	Montblanc Contemporary Collection Key Ring with Montblanc emblem	CS-Cart	P	<p>Stainless steel key ring with brown calf leather</p>
91	Montblanc Nicolas Rieussec Chronograph Automatic	CS-Cart	P	<p>43 mm watch with stainless steel case, Montblanc Calibre MB R200, monopusher chronograph with automatic winding, anthracite-coloured dial with applique hour circle, date display, second time zone with day/night display, chronograph counters with rotating discs.</p>
92	Montblanc TimeWalker Automatic	CS-Cart	P	<p>42 mm stainless steel watch with black dial, red-gold plated hands and numerals, automatic movement, date display, black alligator-skin strap</p>
34	Nike "Untouchable" Women's Tank Top	CS-Cart	P	<div class="text description dark-grey-heather-dark-grey-heather-red"> <div class="product-description-component"> <p><strong>The Nike "Untouchable" Tank Top: Sleeveless tribute to '72</strong></p> Celebrating a banner year in sports with vibrant print detail, the Nike "Untouchable" Women's Tank Top offers a motion-friendly racerback silhouette in a body-hugging fit.<br /> <p><strong>Benefits</strong></p> <li>Slim fit contours to your body</li> <li>Rib scoop-neck and armholes for durability and comfort</li> <li>Racerback for enhanced range of movement</li> <li>Screen-print at front</li> <br /> <p><strong>Additional Details</strong></p> <li>Fabric: 100% cotton</li> <li>Machine wash</li> <li>Imported</li> </div> </div>