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 = 234 
WHERE 
  cscart_products_categories.product_id IN (
    245, 238, 239, 74, 73, 71, 75, 72, 70, 76, 
    67, 86, 106, 105, 101, 69, 125, 120, 117, 
    113, 331, 335, 339, 341, 355, 356, 357, 
    358, 359, 360, 361, 362, 363, 364, 365, 
    366, 367, 368, 369, 370, 379, 380, 381, 
    382, 383, 384, 385, 386, 387, 388, 389, 
    390, 391, 392, 393, 394, 395, 396, 397, 
    398, 399, 400, 401, 402, 403, 404, 405, 
    406, 407, 408, 409, 410, 411, 412, 413, 
    414, 415, 416, 417, 418, 419, 420, 421, 
    422, 423, 424, 425, 426, 68
  ) 
GROUP BY 
  cscart_products_categories.product_id

Query time 0.00132

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "41.25"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "2.59"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id"
            ],
            "rows_examined_per_scan": 84,
            "rows_produced_per_join": 3,
            "filtered": "4.00",
            "cost_info": {
              "read_cost": "19.53",
              "eval_cost": "0.67",
              "prefix_cost": "20.20",
              "data_read_per_join": "8K"
            },
            "used_columns": [
              "category_id",
              "storefront_id",
              "usergroup_ids",
              "status"
            ],
            "attached_condition": "((`pankajecarter_systemfour`.`cscart_categories`.`storefront_id` in (0,1)) and ((`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids`)) and (`pankajecarter_systemfour`.`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": [
              "pankajecarter_systemfour.cscart_categories.category_id"
            ],
            "rows_examined_per_scan": 3,
            "rows_produced_per_join": 2,
            "filtered": "25.69",
            "index_condition": "(`pankajecarter_systemfour`.`cscart_products_categories`.`product_id` in (245,238,239,74,73,71,75,72,70,76,67,86,106,105,101,69,125,120,117,113,331,335,339,341,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,68))",
            "cost_info": {
              "read_cost": "13.34",
              "eval_cost": "0.52",
              "prefix_cost": "35.55",
              "data_read_per_join": "41"
            },
            "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",
              "pankajecarter_systemfour.cscart_products_categories.product_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "2.59",
              "eval_cost": "0.52",
              "prefix_cost": "38.66",
              "data_read_per_join": "41"
            },
            "used_columns": [
              "product_id",
              "category_id",
              "position"
            ]
          }
        }
      ]
    }
  }
}

Result

product_id category_ids position
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
238 235M
239 235M
245 235M
331 235M
335 235M
339 235M
341 235M
355 235M
356 235M
357 235M
358 235M
359 235M
360 235M
361 235M
362 235M
363 235M
364 235M
365 235M
366 235M
367 235M
368 235M
369 235M
370 235M
379 235M
380 235M
381 235M
382 235M
383 235M
384 235M
385 235M
386 235M
387 235M
388 235M
389 235M
390 235M
391 235M
392 235M
393 235M
394 235M
395 235M
396 235M
397 235M
398 235M
399 235M
400 235M
401 235M
402 235M
403 235M
404 235M
405 235M
406 235M
407 235M
408 235M
409 235M
410 235M
411 235M
412 235M
413 235M
414 235M
415 235M
416 235M
417 235M
418 235M
419 235M
420 235M
421 235M
422 235M
423 235M
424 235M
425 235M
426 235M