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 = 40 
WHERE 
  cscart_products_categories.product_id IN (
    51567, 
    51480, 
    51565, 
    51564, 
    51479, 
    139, 
    129, 
    51566, 
    51301, 
    51494, 
    51, 
    50, 
    51497, 
    51498, 
    51495, 
    51563, 
    53, 
    22713, 
    21, 
    22714, 
    22712, 
    46, 
    22715, 
    22716, 
    51496, 
    21567, 
    91, 
    50989, 
    105, 
    48, 
    49, 
    92, 
    137, 
    88, 
    126, 
    31, 
    51358, 
    115, 
    80, 
    37, 
    11, 
    140, 
    41, 
    30, 
    90, 
    38, 
    124, 
    81, 
    56, 
    82, 
    116, 
    117, 
    125, 
    106, 
    39, 
    193, 
    40, 
    22710, 
    22711, 
    17, 
    57, 
    122, 
    58, 
    5, 
    22, 
    18, 
    119, 
    27, 
    121, 
    23, 
    141, 
    4, 
    142, 
    120, 
    62, 
    108, 
    107, 
    94, 
    8, 
    86, 
    54, 
    132, 
    10, 
    93, 
    12, 
    32, 
    7, 
    130, 
    35, 
    16, 
    112, 
    3, 
    78, 
    33, 
    2, 
    110, 
    34, 
    89, 
    114, 
    77, 
    147, 
    36, 
    87, 
    128, 
    96, 
    20, 
    52, 
    84, 
    109, 
    66, 
    118, 
    131, 
    13, 
    19, 
    61, 
    146, 
    95, 
    15, 
    9, 
    85, 
    22421, 
    83, 
    111, 
    59, 
    22424, 
    144, 
    123, 
    60
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00449

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "218.39"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "59.45"
      },
      "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": 59,
            "filtered": "21.56",
            "index_condition": "(`test_uchur_k`.`cscart_products_categories`.`product_id` in (51567,51480,51565,51564,51479,139,129,51566,51301,51494,51,50,51497,51498,51495,51563,53,22713,21,22714,22712,46,22715,22716,51496,21567,91,50989,105,48,49,92,137,88,126,31,51358,115,80,37,11,140,41,30,90,38,124,81,56,82,116,117,125,106,39,193,40,22710,22711,17,57,122,58,5,22,18,119,27,121,23,141,4,142,120,62,108,107,94,8,86,54,132,10,93,12,32,7,130,35,16,112,3,78,33,2,110,34,89,114,77,147,36,87,128,96,20,52,84,109,66,118,131,13,19,61,146,95,15,9,85,22421,83,111,59,22424,144,123,60))",
            "cost_info": {
              "read_cost": "68.94",
              "eval_cost": "5.95",
              "prefix_cost": "138.14",
              "data_read_per_join": "951"
            },
            "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": 59,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "14.86",
              "eval_cost": "5.95",
              "prefix_cost": "158.94",
              "data_read_per_join": "951"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
2 4,40M,42 0
3 4,40M,42 0
4 6,40M,42 0
5 6,40M,42 0
7 7,40M,42 0
8 7,40M,42,390 0
9 6,40M,42 0
10 6,40M,42 0
11 8,40M,42,394 0
12 7,40M,42,390 0
13 6,40M,42 0
15 6,40M,43 0
16 6,40M,44 0
17 6,40M,44 0
18 6,40M,43 0
19 9,40M,44 0
20 9,40M,43 0
21 10,40M,43 0
22 6,40M,42 0
23 6,40M,42 0
27 6,40M,42 0
30 18,40M,44,391 0
31 18,40M,43,391 0
32 7,40M,42 0
33 7,40M,42 0
34 7,40M,42 0
35 7,40M,44 0
36 7,40M,43 0
37 10,40M,42 0
38 10,40M,42 0
39 10,40M,42 0
40 10,40M,44 0
41 10,40M,43 0
46 18,40M,42,391 0
48 10,40M,44 0
49 10,40M,43 0
50 23,40M,44 0
51 23,40M,43 0
52 9,40M,42 0
53 10,40M,44 0
54 24,40M,44 0
56 8,40M,42,394 0
57 6,40M,44 0
58 6,40M,43 0
59 6,40M,42 0
60 6,40M,42 0
61 6,40M,42 0
62 6,40M,42 0
66 4,40M,42 0
77 21,40M 0
78 21,40M 0
80 26,40M 0
81 8,40M,43,394 0
82 8,40M,44,394 0
83 40M,42,312,352 0
84 28,40M,42,394 0
85 28,40M,42,394 0
86 29,40M,43,394 0
87 21,40M 0
88 40M,42,235 0
89 25,40M,42 0
90 9,40M,43 0
91 9,40M,44 0
92 10,40M,42 0
93 9,40M,42 0
94 7,40M,43 0
95 31,40M,43 0
96 32,40M,42 0
105 6,40M,44,394 0
106 6,40M,43,394 0
107 33,40M,42 0
108 33,40M,42 0
109 7,40M,42 0
110 7,40M,42 0
111 7,40M,42 0
112 21,40M,44 0
114 6,40M,42 0
115 8,40M,42,394 0
116 8,40M,42,394 0
117 8,40M,42,394 0
118 6,40M,42 0
119 6,40M,42 0
120 6,40M,42 0
121 6,40M,42 0
122 6,40M,42 0
123 6,40M,42 0
124 7,40M,42 0
125 7,40M,42 0
126 8,40M,42,394 0
128 10,40M,43 0
129 24,40M,42 0
130 10,40M,42 0
131 9,40M,42 0
132 35,40M,42 0
137 7,40M,42 0
139 24,40M,42 0
140 25,40M,42 0
141 6,40M,42 0
142 6,40M,42 0
144 7,40M,42,390 0
146 7,40M,42,390 0
147 7,40M,42 0
193 9,40M,43 0
21567 29M,40,42,394 0
22421 40,133M 0
22424 40,133M 0
22710 18M,40,42,392 0
22711 18M,40,42,392 0
22712 7M,40,42 0
22713 7M,40,42 0
22714 7M,40,42 0
22715 7M,40,42 0
22716 7M,40,42 0
50989 29M,40,42,394 0
51301 24,40M,42 0
51358 18,40M,42,391 0
51479 4M,40,42 0
51480 4M,40,42 0
51494 40,42,204M 0
51495 40M,42,204 0
51496 33M,40 0
51497 33M,40 0
51498 33M,40 0
51563 1M,40,42 0
51564 9M,40,42 0
51565 10,40M,42 0
51566 9M,40,42 0
51567 10M,40,42 0