SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  products.product_type, 
  products.parent_product_id 
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.company_id = 2 
  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 = 2 
  AND products.parent_product_id = 0 
  AND products.product_type != 'D' 
GROUP BY 
  products.product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  480, 96

Query time 0.05609

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 1160.848684,
    "filesort": {
      "sort_key": "descr1.product, products.product_id",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "companies",
              "access_type": "const",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["company_id"],
              "ref": ["const"],
              "rows": 1,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "ALL",
              "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
              "loops": 1,
              "rows": 99,
              "cost": 0.0269918,
              "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)"
            }
          },
          {
            "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.cscart_categories.category_id"],
              "loops": 99,
              "rows": 3954,
              "cost": 64.30465503,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "products",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "status", "idx_parent_product_id"],
              "key": "PRIMARY",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["u985510652_ecartify.products_categories.product_id"],
              "loops": 391446,
              "rows": 1,
              "cost": 354.1898306,
              "filtered": 100,
              "attached_condition": "products.company_id = 2 and products.parent_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.product_type <> 'D'"
            }
          },
          {
            "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.products_categories.product_id",
                "const"
              ],
              "loops": 391446,
              "rows": 1,
              "cost": 363.7253186,
              "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"
              ],
              "key": "usergroup",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["u985510652_ecartify.products_categories.product_id"],
              "loops": 391446,
              "rows": 1,
              "cost": 378.6018874,
              "filtered": 100,
              "attached_condition": "prices.lower_limit = 1 and prices.usergroup_id in (0,0,1)"
            }
          }
        ]
      }
    }
  }
}

Result

product_id product company_name product_type parent_product_id
428427 Lite Book 826 ACME Corp P 0
427728 Lite Book 842 ACME Corp P 0
430953 Lite Book 854 ACME Corp P 0
401174 Lite Book 955 ACME Corp P 0
410003 Lite Book 990 ACME Corp P 0
414674 Lite Bottle 100 ACME Corp P 0
454104 Lite Bottle 162 ACME Corp P 0
409191 Lite Bottle 182 ACME Corp P 0
447293 Lite Bottle 190 ACME Corp P 0
430593 Lite Bottle 277 ACME Corp P 0
420431 Lite Bottle 355 ACME Corp P 0
401982 Lite Bottle 364 ACME Corp P 0
404064 Lite Bottle 369 ACME Corp P 0
412826 Lite Bottle 377 ACME Corp P 0
455161 Lite Bottle 443 ACME Corp P 0
400935 Lite Bottle 489 ACME Corp P 0
449270 Lite Bottle 563 ACME Corp P 0
402248 Lite Bottle 593 ACME Corp P 0
406597 Lite Bottle 628 ACME Corp P 0
433624 Lite Bottle 731 ACME Corp P 0
446080 Lite Bottle 774 ACME Corp P 0
455290 Lite Bottle 792 ACME Corp P 0
427919 Lite Bottle 837 ACME Corp P 0
433335 Lite Bottle 968 ACME Corp P 0
418284 Lite Camera 118 ACME Corp P 0
431392 Lite Camera 175 ACME Corp P 0
437836 Lite Camera 213 ACME Corp P 0
439945 Lite Camera 269 ACME Corp P 0
404692 Lite Camera 303 ACME Corp P 0
455805 Lite Camera 314 ACME Corp P 0
438918 Lite Camera 341 ACME Corp P 0
427972 Lite Camera 373 ACME Corp P 0
404415 Lite Camera 402 ACME Corp P 0
421393 Lite Camera 437 ACME Corp P 0
416786 Lite Camera 511 ACME Corp P 0
457753 Lite Camera 540 ACME Corp P 0
430469 Lite Camera 563 ACME Corp P 0
450047 Lite Camera 568 ACME Corp P 0
448348 Lite Camera 583 ACME Corp P 0
413081 Lite Camera 667 ACME Corp P 0
440081 Lite Camera 721 ACME Corp P 0
460754 Lite Camera 893 ACME Corp P 0
410561 Lite Camera 928 ACME Corp P 0
424471 Lite Chair 142 ACME Corp P 0
455506 Lite Chair 151 ACME Corp P 0
456958 Lite Chair 153 ACME Corp P 0
425735 Lite Chair 167 ACME Corp P 0
457743 Lite Chair 259 ACME Corp P 0
410880 Lite Chair 270 ACME Corp P 0
438251 Lite Chair 369 ACME Corp P 0
445963 Lite Chair 374 ACME Corp P 0
404705 Lite Chair 404 ACME Corp P 0
455281 Lite Chair 503 ACME Corp P 0
417659 Lite Chair 574 ACME Corp P 0
445013 Lite Chair 838 ACME Corp P 0
412981 Lite Chair 979 ACME Corp P 0
419060 Lite Chair 979 ACME Corp P 0
451473 Lite Chair 981 ACME Corp P 0
453502 Lite Chair 998 ACME Corp P 0
410213 Lite Chair 999 ACME Corp P 0
422250 Lite Drone 102 ACME Corp P 0
455802 Lite Drone 142 ACME Corp P 0
409880 Lite Drone 175 ACME Corp P 0
440645 Lite Drone 342 ACME Corp P 0
405789 Lite Drone 456 ACME Corp P 0
446310 Lite Drone 507 ACME Corp P 0
450867 Lite Drone 516 ACME Corp P 0
406164 Lite Drone 568 ACME Corp P 0
426321 Lite Drone 612 ACME Corp P 0
427080 Lite Drone 617 ACME Corp P 0
406770 Lite Drone 646 ACME Corp P 0
408843 Lite Drone 881 ACME Corp P 0
406290 Lite Drone 898 ACME Corp P 0
416109 Lite Grinder 149 ACME Corp P 0
428493 Lite Grinder 218 ACME Corp P 0
452275 Lite Grinder 266 ACME Corp P 0
456156 Lite Grinder 286 ACME Corp P 0
413521 Lite Grinder 388 ACME Corp P 0
438629 Lite Grinder 451 ACME Corp P 0
429191 Lite Grinder 490 ACME Corp P 0
403689 Lite Grinder 557 ACME Corp P 0
409425 Lite Grinder 596 ACME Corp P 0
459278 Lite Grinder 640 ACME Corp P 0
437741 Lite Grinder 745 ACME Corp P 0
417942 Lite Grinder 840 ACME Corp P 0
430408 Lite Headphones 162 ACME Corp P 0
433066 Lite Headphones 248 ACME Corp P 0
439497 Lite Headphones 279 ACME Corp P 0
448196 Lite Headphones 424 ACME Corp P 0
426522 Lite Headphones 441 ACME Corp P 0
448198 Lite Headphones 585 ACME Corp P 0
424292 Lite Headphones 696 ACME Corp P 0
446957 Lite Headphones 730 ACME Corp P 0
426562 Lite Headphones 807 ACME Corp P 0
405927 Lite Headphones 821 ACME Corp P 0
458299 Lite Headphones 874 ACME Corp P 0