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 (196, 199, 197, 198, 200) 
  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 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 
  12, 12

Query time 0.00215

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.49"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "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 (196,199,197,198,200))",
              "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": "42.58",
              "cost_info": {
                "read_cost": "0.60",
                "eval_cost": "0.05",
                "prefix_cost": "13.06",
                "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`.`parent_product_id` = 0) 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`.`company_id` in ('1','2','3','4','5','6')) and (`atulecarter_atul_demo1`.`products`.`product_type` <> 'D'))"
            }
          },
          {
            "table": {
              "table_name": "companies",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "company_id"
              ],
              "key_length": "4",
              "ref": [
                "atulecarter_atul_demo1.products.company_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "16.67",
              "cost_info": {
                "read_cost": "0.26",
                "eval_cost": "0.01",
                "prefix_cost": "13.37",
                "data_read_per_join": "306"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ],
              "attached_condition": "(`atulecarter_atul_demo1`.`companies`.`status` = 'A')"
            }
          },
          {
            "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.04",
                "eval_cost": "0.01",
                "prefix_cost": "13.42",
                "data_read_per_join": "198"
              },
              "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.02",
                "prefix_cost": "13.49",
                "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)))"
            }
          }
        ]
      }
    }
  }
}

Result

product_id	product	company_name	product_type	full_description
166	Nikon 1 J1 Two-Lens Wide Angle Kit Red	CS-Cart	P	<p>For your very mobile lifestyle comes a completely new imaging system with freedom to create and express as never before. Highly compact and portable, the Nikon 1 J1 goes everywhere you go. Moments from your amazing world are captured using a highly responsive autofocus and super fast shooting speed. In one touch you can work in Full HD (1080p) movie record mode. A camera so versatile you can even snap photos while recording movies. Unleash your creativity with Nikon 1—offering a balance of high performance, advanced features, portability and a newly designed interchangeable lens system. Infuse your world with photos from every moment and connect as never before.</p>
163	NX200 20.3 Megapixel Compact System Camera	CS-Cart	P	<p>Capture every moment in true-to-life quality. With the best-in-class resolution of the extra large 20.3 MP APS-C CMOS sensor, shots are rich, deep and exquisitely detailed. For active subjects, stills can be captured beautifully with 7fps Continuous Shot/100ms AF Speed. Then review your shot on the large 3.0” AMOLED display—made for sharper images and glare reduction.</p>
184	Samsung 30mm NX Pancake Lens	CS-Cart	P	<p>Samsung’s compact 30mm pancake lens is the brightest in its class. With its 30mm fixed focal length and maximum aperture of F/2, you can capture exceptionally bright images — even in low-light situations such as shady forest paths and indoor weddings. You can also create striking, images with shallow depth of field in a variety of settings. And since it’s incredibly slim and lightweight, you can take it with you everywhere.</p>
183	Samsung 50-200mm NX Telephoto OIS Lens	CS-Cart	P	<p>The Samsung T50200SB 50-200mm Telephoto OIS Lens is a long telephoto zoom lens compatible with our NX Series of digital cameras.</p>
164	WB210 10MB 14 Megapixel Slim Digital Camera (Red)	CS-Cart	P	<p>When shooting in low light, zooming in on a shot, or when your camera is unsteady, Optical Image Stabilization helps eliminate blurry results. This feature stabilizes your photos by changing their path to the image sensor before they are recorded digitally. Your photos come out sharp and ideally focused.</p>