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 = 166 
WHERE 
  cscart_products_categories.product_id IN (
    148, 
    180, 
    18, 
    16, 
    4, 
    5, 
    23, 
    24, 
    1, 
    22, 
    149, 
    227, 
    190, 
    189, 
    245, 
    238, 
    239, 
    242, 
    243, 
    241, 
    240, 
    214, 
    217, 
    218, 
    207, 
    59, 
    58, 
    60, 
    172, 
    134, 
    135, 
    168, 
    167, 
    169, 
    42, 
    39, 
    40, 
    205, 
    37, 
    74, 
    73, 
    71, 
    75, 
    72, 
    70, 
    156, 
    175, 
    43, 
    76, 
    56, 
    57, 
    30, 
    246, 
    208, 
    173, 
    229, 
    211, 
    210, 
    209, 
    67, 
    25, 
    213, 
    215, 
    226, 
    154, 
    53, 
    52, 
    228, 
    161, 
    165, 
    166, 
    86, 
    106, 
    105, 
    247, 
    101, 
    33, 
    32, 
    163, 
    244, 
    212, 
    155, 
    55, 
    69, 
    206, 
    184, 
    183, 
    133, 
    132, 
    125, 
    120, 
    223, 
    225, 
    224, 
    146, 
    147, 
    145, 
    117, 
    219, 
    222, 
    220, 
    221, 
    113, 
    157, 
    68, 
    29, 
    6, 
    8, 
    14, 
    9, 
    10, 
    19, 
    21, 
    50, 
    51, 
    49, 
    48, 
    54, 
    153, 
    164, 
    248, 
    65, 
    64, 
    63, 
    62, 
    66, 
    140, 
    141
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00378

JSON explain

{
  "query_block": {
    "select_id": 1,
    "read_sorted_file": {
      "filesort": {
        "sort_key": "cscart_products_categories.product_id",
        "table": {
          "table_name": "cscart_products_categories",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "pt"],
          "rows": 267,
          "filtered": 54.30711746,
          "attached_condition": "cscart_products_categories.product_id in (148,180,18,16,4,5,23,24,1,22,149,227,190,189,245,238,239,242,243,241,240,214,217,218,207,59,58,60,172,134,135,168,167,169,42,39,40,205,37,74,73,71,75,72,70,156,175,43,76,56,57,30,246,208,173,229,211,210,209,67,25,213,215,226,154,53,52,228,161,165,166,86,106,105,247,101,33,32,163,244,212,155,55,69,206,184,183,133,132,125,120,223,225,224,146,147,145,117,219,222,220,221,113,157,68,29,6,8,14,9,10,19,21,50,51,49,48,54,153,164,248,65,64,63,62,66,140,141)"
        }
      }
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["category_id"],
      "ref": ["c.cscart_products_categories.category_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "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')"
    },
    "table": {
      "table_name": "product_position_source",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "PRIMARY",
      "key_length": "6",
      "used_key_parts": ["category_id", "product_id"],
      "ref": ["const", "c.cscart_products_categories.product_id"],
      "rows": 1,
      "filtered": 100
    }
  }
}

Result

product_id category_ids position
1 190M
4 190M
5 191M
6 191M
8 190,193M
9 190,193M
10 193M,190
14 191M
16 194M
18 194M
19 194M
21 194M
22 195M
23 195M
24 195M
25 195M
29 176M
30 176M
32 176M
33 176M
37 185M
39 185M
40 185M
42 185M
43 185M
48 186M
49 186M
50 186M
51 186M
52 202M
53 202M
54 202M
55 187M
56 187M
57 187M
58 188M
59 188M
60 176M
62 189M
63 189M
64 189M
65 189M
66 189M
67 236M
68 236M
69 236M
70 236M
71 236M
72 240M
73 240M
74 240M
75 240M
76 240M
86 238M
101 238M
105 238M
106 238M
113 237M
117 237M
120 237M
125 237M
132 182M,179
133 179M,182
134 179,182M
135 179,182M
140 180M
141 180M
145 179M
146 179M
147 179M
148 179M
149 179M
153 181M
154 181M
155 181M
156 181M
157 181M
161 199,198M
163 198M,199
164 198M,199
165 198M,199
166 198M,199
167 198,197M,199
168 199,198,197M
169 197M,198,199
172 199M
173 199M
175 199M
180 200M
183 200M
184 200M
189 200M
190 200M
205 171M
206 171M
207 171M
208 171M
209 172M
210 172M
211 172M
212 172M
213 201M
214 168M
215 201M
217 168M
218 168M
219 169M
220 169M
221 169M
222 169M
223 165M
224 165M
225 165M
226 201M
227 170M
228 170M
229 170M
238 235M
239 235M
240 165M
241 165M
242 178M
243 178M
244 168M
245 235M
246 199M
247 165M
248 248M,255