SELECT 
  pf.feature_id, 
  pf.company_id, 
  pf.feature_type, 
  pf.parent_id, 
  pf.display_on_product, 
  pf.display_on_catalog, 
  pf.display_on_header, 
  cscart_product_features_descriptions.description, 
  cscart_product_features_descriptions.internal_name, 
  cscart_product_features_descriptions.lang_code, 
  cscart_product_features_descriptions.prefix, 
  cscart_product_features_descriptions.suffix, 
  pf.categories_path, 
  cscart_product_features_descriptions.full_description, 
  pf.status, 
  pf.comparison, 
  pf.position, 
  pf.purpose, 
  pf.feature_style, 
  pf.filter_style, 
  pf.feature_code, 
  pf.timestamp, 
  pf.updated_timestamp, 
  pf_groups.position AS group_position, 
  cscart_product_features_values.value, 
  cscart_product_features_values.variant_id, 
  cscart_product_features_values.value_int, 
  abt_filters.filter_id 
FROM 
  cscart_product_features AS pf 
  LEFT JOIN cscart_product_features AS pf_groups ON pf.parent_id = pf_groups.feature_id 
  LEFT JOIN cscart_product_features_descriptions AS pf_groups_description ON pf_groups_description.feature_id = pf.parent_id 
  AND pf_groups_description.lang_code = 'en' 
  LEFT JOIN cscart_product_features_descriptions ON cscart_product_features_descriptions.feature_id = pf.feature_id 
  AND cscart_product_features_descriptions.lang_code = 'en' 
  INNER JOIN cscart_product_features_values ON cscart_product_features_values.feature_id = pf.feature_id 
  AND cscart_product_features_values.product_id = 224454 
  AND cscart_product_features_values.lang_code = 'en' 
  LEFT JOIN cscart_product_filters AS abt_filters ON abt_filters.feature_id = pf.feature_id 
WHERE 
  1 = 1 
  AND pf.feature_type != 'G' 
  AND pf.status IN ('A') 
  AND (
    pf_groups.status IN ('A') 
    OR pf_groups.status IS NULL
  ) 
  AND pf.company_id IN (0, 430) 
  AND pf.display_on_product = 'Y' 
  AND (
    pf.categories_path = '' 
    OR ISNULL(pf.categories_path) 
    OR FIND_IN_SET(266, pf.categories_path) 
    OR FIND_IN_SET(327, pf.categories_path) 
    OR FIND_IN_SET(1695, pf.categories_path) 
    OR FIND_IN_SET(2599, pf.categories_path)
  ) 
GROUP BY 
  pf.feature_id 
ORDER BY 
  group_position asc, 
  pf_groups_description.description asc, 
  pf_groups.feature_id asc, 
  pf.position asc, 
  cscart_product_features_descriptions.description asc, 
  pf.feature_id asc

Query time 0.00766

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "292.90"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "grouping_operation": {
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "pf",
              "access_type": "index",
              "possible_keys": [
                "PRIMARY",
                "status",
                "company_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "feature_id"
              ],
              "key_length": "3",
              "rows_examined_per_scan": 540,
              "rows_produced_per_join": 44,
              "filtered": "8.15",
              "cost_info": {
                "read_cost": "105.20",
                "eval_cost": "8.80",
                "prefix_cost": "114.00",
                "data_read_per_join": "18K"
              },
              "used_columns": [
                "feature_id",
                "feature_code",
                "company_id",
                "purpose",
                "feature_style",
                "filter_style",
                "feature_type",
                "categories_path",
                "parent_id",
                "display_on_product",
                "display_on_catalog",
                "display_on_header",
                "status",
                "position",
                "comparison",
                "timestamp",
                "updated_timestamp"
              ],
              "attached_condition": "((`webmarco`.`pf`.`feature_type` <> 'G') and (`webmarco`.`pf`.`status` = 'A') and (`webmarco`.`pf`.`company_id` in (0,430)) and (`webmarco`.`pf`.`display_on_product` = 'Y') and ((`webmarco`.`pf`.`categories_path` = '') or isnull(`webmarco`.`pf`.`categories_path`) or find_in_set(266,`webmarco`.`pf`.`categories_path`) or find_in_set(327,`webmarco`.`pf`.`categories_path`) or find_in_set(1695,`webmarco`.`pf`.`categories_path`) or find_in_set(2599,`webmarco`.`pf`.`categories_path`)))"
            }
          },
          {
            "table": {
              "table_name": "cscart_product_features_values",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "fl",
                "lang_code",
                "product_id",
                "fpl",
                "idx_product_feature_variant_id"
              ],
              "key": "fpl",
              "used_key_parts": [
                "feature_id",
                "product_id",
                "lang_code"
              ],
              "key_length": "12",
              "ref": [
                "webmarco.pf.feature_id",
                "const",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 44,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "44.01",
                "eval_cost": "8.80",
                "prefix_cost": "166.81",
                "data_read_per_join": "34K"
              },
              "used_columns": [
                "feature_id",
                "product_id",
                "variant_id",
                "value",
                "value_int",
                "lang_code"
              ]
            }
          },
          {
            "table": {
              "table_name": "abt_filters",
              "access_type": "ref",
              "possible_keys": [
                "feature_id"
              ],
              "key": "feature_id",
              "used_key_parts": [
                "feature_id"
              ],
              "key_length": "3",
              "ref": [
                "webmarco.pf.feature_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 44,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "44.01",
                "eval_cost": "8.97",
                "prefix_cost": "219.79",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "filter_id",
                "feature_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "pf_groups",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "feature_id"
              ],
              "key_length": "3",
              "ref": [
                "webmarco.pf.parent_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 8,
              "filtered": "19.00",
              "cost_info": {
                "read_cost": "44.01",
                "eval_cost": "1.70",
                "prefix_cost": "272.77",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "feature_id",
                "status",
                "position"
              ],
              "attached_condition": "<if>(found_match(pf_groups), ((`webmarco`.`pf_groups`.`status` = 'A') or isnull(`webmarco`.`pf_groups`.`status`)), true)"
            }
          },
          {
            "table": {
              "table_name": "pf_groups_description",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "feature_id",
                "lang_code"
              ],
              "key_length": "9",
              "ref": [
                "webmarco.pf.parent_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 8,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "8.36",
                "eval_cost": "1.70",
                "prefix_cost": "282.83",
                "data_read_per_join": "19K"
              },
              "used_columns": [
                "feature_id",
                "description",
                "lang_code"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_product_features_descriptions",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "feature_id",
                "lang_code"
              ],
              "key_length": "9",
              "ref": [
                "webmarco.pf.feature_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 8,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "8.36",
                "eval_cost": "1.70",
                "prefix_cost": "292.90",
                "data_read_per_join": "19K"
              },
              "used_columns": [
                "feature_id",
                "description",
                "full_description",
                "prefix",
                "suffix",
                "lang_code",
                "internal_name"
              ]
            }
          }
        ]
      }
    }
  }
}

Result

feature_id company_id feature_type parent_id display_on_product display_on_catalog display_on_header description internal_name lang_code prefix suffix categories_path full_description status comparison position purpose feature_style filter_style feature_code timestamp updated_timestamp group_position value variant_id value_int filter_id
741 0 M 740 Y Y N Type of Service Type of Service en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 0 find_products text checkbox 1620375578 1644923304 0 38299 343
835 0 M 740 Y Y N Country Country en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 1 find_products multiple_checkbox checkbox 1623321099 1641980965 0 59704 373
834 0 M 740 Y Y N Region / District Region / District en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 2 find_products multiple_checkbox checkbox 1623059078 1642065685 0 59741 395
837 0 M 740 Y Y N Abruzzo Provinces Abruzzo Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 4 find_products multiple_checkbox checkbox 1624524985 1631804865 0 59777 394
838 0 M 740 Y Y N Basilicata Provinces Basilicata Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 6 find_products multiple_checkbox checkbox 1624537161 1624537161 0 59832 375
839 0 M 740 Y Y N Calabria Provinces Calabria Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 7 find_products multiple_checkbox checkbox 1624537554 1624537554 0 59834 376
840 0 M 740 Y Y N Campania Provinces Campania Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 9 find_products multiple_checkbox checkbox 1624542290 1624542388 0 59842 377
841 0 M 740 Y Y N Emilia-Romagna Provinces Emilia-Romagna Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 10 find_products multiple_checkbox checkbox 1624542555 1624547043 0 59847 378
842 0 M 740 Y Y N Friuli-Venezia Giulia Provinces Friuli-Venezia Giulia Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 10 find_products multiple_checkbox checkbox 1624542750 1624542750 0 59857 379
843 0 M 740 Y Y N Lazio Provinces Lazio Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 11 find_products multiple_checkbox checkbox 1624542872 1640019743 0 59861 380
844 0 M 740 Y Y N Liguria Provinces Liguria Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 12 find_products multiple_checkbox checkbox 1624543027 1624543027 0 59866 381
845 0 M 740 Y Y N Lombardia Provinces Lombardia Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 14 find_products multiple_checkbox checkbox 1624543338 1624543338 0 59870 382
846 0 M 740 Y Y N Marche Provinces Marche Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 15 find_products multiple_checkbox checkbox 1624544365 1624544365 0 59882 383
847 0 M 740 Y Y N Molise Provinces Molise Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 15 find_products multiple_checkbox checkbox 1624544509 1624544509 0 59888 384
848 0 M 740 Y Y N Piemonte Provinces Piemonte Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 17 find_products multiple_checkbox checkbox 1624544677 1624547320 0 59890 385
849 0 M 740 Y Y N Puglia Provinces Puglia Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 18 find_products multiple_checkbox checkbox 1624544834 1624544834 0 59898 386
850 0 M 740 Y Y N Sardegna Provinces Sardegna Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 20 find_products multiple_checkbox checkbox 1624544996 1624544996 0 59906 387
851 0 M 740 Y Y N Sicilia Provinces Sicilia Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 21 find_products multiple_checkbox checkbox 1624545130 1624545130 0 59911 388
852 0 M 740 Y Y N Trentino-Alto Adige Provinces Trentino-Alto Adige Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 22 find_products multiple_checkbox checkbox 1624545188 1624545188 0 59920 389
853 0 M 740 Y Y N Toscana Provinces Toscana Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 23 find_products multiple_checkbox checkbox 1624545334 1639057528 0 59922 390
854 0 M 740 Y Y N Umbria Provinces Umbria Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 24 find_products multiple_checkbox checkbox 1624545454 1624545454 0 59932 391
855 0 M 740 Y Y N Valle d'Aosta Provinces Valle d'Aosta Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 26 find_products multiple_checkbox checkbox 25 1624545510 1624547170 0 59934 392
856 0 M 740 Y Y N Veneto Provinces Veneto Provinces en 266,327,341,344,356,394,443,715,930,990,1479,1498,328,345,346,347,348,349,352,1018,1438,1584,1695,1907,1936,2171,2178 A N 27 find_products multiple_checkbox checkbox 1624545712 1624545712 0 59935 393