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 companies.status IN ('A') 
  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 (
    (
      1 
      AND products.product_id IN (12, 39, 51, 52, 237, 230, 234)
    ) 
    AND companies.status IN ('A') 
    AND prices.usergroup_id IN (0, 0, 1)
  ) 
  AND products.company_id IN('1', '2', '3', '4', '5', '6') 
GROUP BY 
  products.product_id 
ORDER BY 
  product asc, 
  products.product_id ASC

Query time 0.00235

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "23.11"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "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": "products",
              "access_type": "range",
              "possible_keys": [
                "PRIMARY",
                "status"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "rows_examined_per_scan": 7,
              "rows_produced_per_join": 0,
              "filtered": "4.97",
              "index_condition": "((`atulecarter_atul_demo1`.`products`.`product_id` in (12,39,51,52,237,230,234)) and (`atulecarter_atul_demo1`.`products`.`product_id` is not null))",
              "using_join_buffer": "Block Nested Loop",
              "cost_info": {
                "read_cost": "16.11",
                "eval_cost": "0.07",
                "prefix_cost": "20.28",
                "data_read_per_join": "1K"
              },
              "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'))"
            }
          },
          {
            "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.product_id"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 0,
              "filtered": "8.79",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.37",
                "eval_cost": "0.02",
                "prefix_cost": "20.86",
                "data_read_per_join": "2"
              },
              "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)) and (`atulecarter_atul_demo1`.`prices`.`usergroup_id` in (0,0,1)))"
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "pt"
              ],
              "key": "pt",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "atulecarter_atul_demo1.products.product_id"
              ],
              "rows_examined_per_scan": 11,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.83",
                "eval_cost": "0.20",
                "prefix_cost": "21.88",
                "data_read_per_join": "16"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "atulecarter_atul_demo1.products_categories.category_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "5.00",
              "cost_info": {
                "read_cost": "1.01",
                "eval_cost": "0.01",
                "prefix_cost": "23.09",
                "data_read_per_join": "134"
              },
              "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": "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.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.01",
                "prefix_cost": "23.11",
                "data_read_per_join": "235"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product",
                "full_description"
              ]
            }
          }
        ]
      }
    }
  }
}

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>
230	2011 Pit Boss	CS-Cart	P	<p>Hydration Capacity: 100 oz (3 L)<br /><br />Total Capacity: 1800 cu in (29.50 L)<br /><br />CamelBak® Got Your Bak™ Guarantee: If we built it, we'll Bak it™ with our lifetime guarantee. <br /><br />Reservoir Features: Quick Link™ System, 1/4 turn - easy open/close cap, lightweight fillport, dryer arms, center baffling and low-profile design, patented Big Bite™ Valve, HydroGuard™ technology, insulated PureFlow™ tube, easy-to-clean wide-mouth opening<br /><br />BACK PANEL: Air Director™ Snowshed™<br /><br />HARNESS: Therminator™ provides easy access for frequent sipping, insulated and fully enclosed to protect against freezing<br /><br />BELT: Load-bearing with cargo<br /><br />Additional Features: <br />Versatile Snowboard / Ski Carry (Diagonal, Vertical or Horizontal), tri-zip quick gear access, gear compression, goggle pocket, essentials pocket<br />Drop-out Probe Pocket for instant probe access -- locate survivors without unloading your pack<br /><br />Designed to carry: skis/snowboard, shovel, probe, skins, snowshoes, helmet, goggles, extra layers, lunch, tools, camera, phone, wallet, keys</p>
39	DEH-80PRS	CS-Cart	P	<p>The DEH-80PRS CD receiver features audiophile grade internal components and materials that are carefully selected to achieve Pioneer's highest standards for the ultimate in sound quality. Pioneer’s exclusive sound field technologies including Auto EQ and Auto Time Alignment help optimize audio in ways that perfectly suit particular listening spaces and allow subtle manual control of settings. Versatile connectivity helps deliver exceptional sound quality from various digital devices.</p>
237	Elite Evanston 8 Tent	CS-Cart	P	<p>• 8 person/1 room tent<br />• 12'x12' footprint<br />• Exclusive WeatherTec™ System<br />Keeps you dry -- Guaranteed™<br />• Modified dome structure, easy to transport and simple to set up<br />• 6 ft. 2 in. center height<br />• Hinged door (patent pending)<br />• Great for family camping, scout leaders, extended camping trips<br />• Self-rolling windows (patent pending)<br />• Tent includes remote controlled light<br />• Light is 100 lumens<br />• Control airflow with Variflo™ adjustable ventilation<br />• Privacy vent window<br />• Interior gear pocket<br />• Electrical access port<br />• Front Porch and Wings provide great outdoor living space<br />• Easy set up with continuous, color coded pole sleeves and shock-corded poles<br />• Easy instructions sewn into durable carry bag<br />• Carry bag also includes separate sacks for poles and stakes<br />• Fly: Polyester taffeta 75D <br />• Mesh: Polyester 68D inner tent<br />• Floor: Polyethylene 1000D-140g/sqm floor<br />• Poles: 11 mm, 7.9 mm and 6.3 mm fiberglass<br />• Limited 1 year warranty<br />• Made in China</p>
52	KFC-W3013PS	CS-Cart	P	<p>1200W Peak Power<br /> 400W RMS Power<br /> 4 ohms Impedance<br /> 34Hz - 300Hz Frequency Response<br /> 6" Mounting Depth<br /> PP Cone with Diamond Array Pattern<br /> Dual-Ventilation System<br /> Rubber Surround</p>
51	TS-D6902R	CS-Cart	P	<p>Absolute fidelity to musical sources takes form from speakers that reproduce the ambience in which sounds originate. Stage size, musicians’ movement, aural reflection and other distinctive details all bring these sounds to life. Pioneer D-Series speakers are available in 2-way component packages or 2-way coaxial designs in multiple sizes that fit most vehicles.</p>
234	White Water™ Cool Weather Sleeping Bag	CS-Cart	P	<p>• Coleman® cool weather sleeping bags made for camping temperatures between 30 and 50 degrees<br />• Rectangle shape, big & tall 39” x 84”, fits up to 6’4” tall<br />• 4 pounds of Coletherm® insulation<br />• Polyester cover with cotton flannel liner<br />• Groundbreaking headrest shape makes keeping your head off the ground or floor easy<br />• Essential camping gear, sleeping bag for a better night outdoors<br />• Don’t forget the Coleman® camping tent to go with your sleeping bags<br />• ComfortSmart™ Technology includes:<br />• ZipPlow™ plows fabric away from zipper to prevent snags<br />• ComfortCuff™ surrounds your face with softness<br />• Roll Control™ locks bag in place for easier rolling<br />• FiberLock™ prevents insulation from shifting, increases durability<br />• ThermoLock™ construction reduces heat loss through the zipper, keeping you warmer<br />• ZipperGlide™ tailoring allows smooth zipper operation around the corners<br />• QuickCord™ for easy, no tie closure<br />• Commercial Machine washable<br />• Five year limited warranty<br />• Made in China</p>