SELECT 
  cscart_products_categories.product_id, 
  GROUP_CONCAT(
    IF(
      cscart_products_categories.link_type = "M", 
      CONCAT(
        cscart_products_categories.category_id, 
        "M"
      ), 
      cscart_products_categories.category_id
    )
  ) AS category_ids, 
  product_position_source.position AS position 
FROM 
  cscart_products_categories 
  INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id 
  AND cscart_categories.storefront_id IN (0, 1) 
  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') 
  LEFT JOIN cscart_products_categories AS product_position_source ON cscart_products_categories.product_id = product_position_source.product_id 
  AND product_position_source.category_id = 5 
WHERE 
  cscart_products_categories.product_id IN (
    51567, 
    51565, 
    51564, 
    139, 
    129, 
    51566, 
    51301, 
    51, 
    50, 
    51497, 
    51498, 
    53, 
    22713, 
    21, 
    22714, 
    22712, 
    46, 
    22715, 
    22716, 
    51496, 
    21567, 
    91, 
    50989, 
    47, 
    105, 
    48, 
    49, 
    92, 
    137, 
    88, 
    126, 
    31, 
    51358, 
    115, 
    37, 
    11, 
    140, 
    41, 
    30, 
    90, 
    38, 
    124, 
    81, 
    56, 
    135, 
    82, 
    116, 
    117, 
    125, 
    106, 
    39, 
    193, 
    65, 
    40, 
    22710, 
    22711, 
    17, 
    57, 
    122, 
    58, 
    5, 
    22, 
    18, 
    27, 
    121, 
    119, 
    141, 
    23, 
    4, 
    142, 
    120, 
    62, 
    108, 
    107, 
    94, 
    8, 
    86, 
    54, 
    10, 
    93, 
    12, 
    32, 
    7, 
    130, 
    35, 
    16, 
    33, 
    110, 
    34, 
    89, 
    147, 
    114, 
    36, 
    128, 
    20, 
    52, 
    84, 
    109, 
    118, 
    19, 
    131, 
    146, 
    13, 
    61, 
    95, 
    15, 
    9, 
    85, 
    59, 
    111, 
    144, 
    60, 
    123, 
    145, 
    113, 
    6, 
    14, 
    63, 
    136, 
    50984, 
    127, 
    134, 
    42, 
    50985, 
    50986, 
    50988, 
    50993, 
    50992
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00417

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "216.59"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "58.12"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 383,
            "rows_produced_per_join": 15,
            "filtered": "4.00",
            "cost_info": {
              "read_cost": "40.09",
              "eval_cost": "1.53",
              "prefix_cost": "41.62",
              "data_read_per_join": "81K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`test_uchur_k`.`cscart_categories`.`storefront_id` in (0,1)) and ((`test_uchur_k`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`test_uchur_k`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`test_uchur_k`.`cscart_categories`.`usergroup_ids`))) and (`test_uchur_k`.`cscart_categories`.`status` in ('A','H')))"
          }
        },
        {
          "table": {
            "table_name": "cscart_products_categories",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "ref": [
              "test_uchur_k.cscart_categories.category_id"
            ],
            "rows_examined_per_scan": 18,
            "rows_produced_per_join": 58,
            "filtered": "21.08",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (51567,51565,51564,139,129,51566,51301,51,50,51497,51498,53,22713,21,22714,22712,46,22715,22716,51496,21567,91,50989,47,105,48,49,92,137,88,126,31,51358,115,37,11,140,41,30,90,38,124,81,56,135,82,116,117,125,106,39,193,65,40,22710,22711,17,57,122,58,5,22,18,27,121,119,141,23,4,142,120,62,108,107,94,8,86,54,10,93,12,32,7,130,35,16,33,110,34,89,147,114,36,128,20,52,84,109,118,19,131,146,13,61,95,15,9,85,59,111,144,60,123,145,113,6,14,63,136,50984,127,134,42,50985,50986,50988,50993,50992))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "5.81",
              "prefix_cost": "138.14",
              "data_read_per_join": "929"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "link_type"
            ]
          }
        },
        {
          "table": {
            "table_name": "product_position_source",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "pt"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "product_id"
            ],
            "key_length": "6",
            "ref": [
              "const",
              "test_uchur_k.cscart_products_categories.product_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 58,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "14.53",
              "eval_cost": "5.81",
              "prefix_cost": "158.48",
              "data_read_per_join": "929"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
4 6,40M,42
5 6,40M,42
6 6,40M,42
7 7,40M,42
8 7,40M,42,390
9 6,40M,42
10 6,40M,42
11 8,40M,42,394
12 7,40M,42,390
13 6,40M,42
14 6,40M,42
15 6,40M,43
16 6,40M,44
17 6,40M,44
18 6,40M,43
19 9,40M,44
20 9,40M,43
21 10,40M,43
22 6,40M,42
23 6,40M,42
27 6,40M,42
30 18,40M,44,391
31 18,40M,43,391
32 7,40M,42
33 7,40M,42
34 7,40M,42
35 7,40M,44
36 7,40M,43
37 10,40M,42
38 10,40M,42
39 10,40M,42
40 10,40M,44
41 10,40M,43
42 18M,45,391
46 18,40M,42,391
47 23M,45
48 10,40M,44
49 10,40M,43
50 23,40M,44
51 23,40M,43
52 9,40M,42
53 10,40M,44
54 24,40M,44
56 8,40M,42,394
57 6,40M,44
58 6,40M,43
59 6,40M,42
60 6,40M,42
61 6,40M,42
62 6,40M,42
63 8M,45,394
65 25M,45
81 8,40M,43,394
82 8,40M,44,394
84 28,40M,42,394
85 28,40M,42,394
86 29,40M,43,394
88 40M,42,235
89 25,40M,42
90 9,40M,43
91 9,40M,44
92 10,40M,42
93 9,40M,42
94 7,40M,43
95 31,40M,43
105 6,40M,44,394
106 6,40M,43,394
107 33,40M,42
108 33,40M,42
109 7,40M,42
110 7,40M,42
111 7,40M,42
113 6,40M,42
114 6,40M,42
115 8,40M,42,394
116 8,40M,42,394
117 8,40M,42,394
118 6,40M,42
119 6,40M,42
120 6,40M,42
121 6,40M,42
122 6,40M,42
123 6,40M,42
124 7,40M,42
125 7,40M,42
126 8,40M,42,394
127 34M,45
128 10,40M,43
129 24,40M,42
130 10,40M,42
131 9,40M,42
134 10,45M
135 36M,45,394
136 37M,45,394
137 7,40M,42
139 24,40M,42
140 25,40M,42
141 6,40M,42
142 6,40M,42
144 7,40M,42,390
145 7,40M,42,390
146 7,40M,42,390
147 7,40M,42
193 9,40M,43
21567 29M,40,42,394
22710 18M,40,42,392
22711 18M,40,42,392
22712 7M,40,42
22713 7M,40,42
22714 7M,40,42
22715 7M,40,42
22716 7M,40,42
50984 10M,45
50985 10M
50986 10M
50988 10M
50989 29M,40,42,394
50992 25M
50993 25M
51301 24,40M,42
51358 18,40M,42,391
51496 33M,40
51497 33M,40
51498 33M,40
51564 9M,40,42
51565 10,40M,42
51566 9M,40,42
51567 10M,40,42