SELECT 
  pc.product_id 
FROM 
  cscart_products_categories AS pc 
WHERE 
  pc.category_id IN (
    SELECT 
      c.category_id 
    FROM 
      cscart_categories AS c 
      LEFT JOIN cscart_products AS p ON p.product_id = pc.product_id 
    WHERE 
      c.parent_id = 294 
      AND p.status = 'A'
  )

Query time 0.00083

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "231.17"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "c",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "parent",
            "p_category_id"
          ],
          "key": "parent",
          "used_key_parts": [
            "parent_id"
          ],
          "key_length": "3",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.50",
            "prefix_cost": "0.75",
            "data_read_per_join": "54K"
          },
          "used_columns": [
            "category_id",
            "parent_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "pc",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "pt",
            "idx_pc_product_id_type"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "category_id"
          ],
          "key_length": "3",
          "ref": [
            "dev2.c.category_id"
          ],
          "rows_examined_per_scan": 101,
          "rows_produced_per_join": 508,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1.66",
            "eval_cost": "50.84",
            "prefix_cost": "53.25",
            "data_read_per_join": "7K"
          },
          "used_columns": [
            "product_id",
            "category_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "p",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "status",
            "cscart_products_product_id_index",
            "idx_status"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "dev2.pc.product_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 367,
          "filtered": "72.23",
          "cost_info": {
            "read_cost": "127.09",
            "eval_cost": "36.72",
            "prefix_cost": "231.17",
            "data_read_per_join": "2M"
          },
          "used_columns": [
            "product_id",
            "status"
          ],
          "attached_condition": "(`dev2`.`p`.`status` = 'A')"
        }
      }
    ]
  }
}

Result

product_id
11373
11374
11375
11376
12135
12137
12499
13551
13553
13646
14827
16069
16483
16514
16527
16528
16538
16541
16557
16784
16791
16794
16795
16803
16840
16874
16946
16959
16969
17399
17404
17443
18024
18025
18035
18036
18037
18039
18040
18041
18042
18138
18141
18142
18150
18153
18354
18357
18358
18404
18429
18430
18431
18438
18439
18440
18441
18442
18443
18444
18500
18503
18504
18507
18539
18541
18542
18543
18544
18546
18551
18561
18568
18576
18577
18578
18580
18582
18589
18602
18604
18606
18679
18703
18934
19444
19447
19653
19654
20651
20671
20824
21087
21088
21089
21090
21707
21764
21768
21769
21770
21771
21796
21918
21932
22019
22053
22095
22108
22219
22221
22239
22242
22260
22346
22489
22549
22773
22814
22883
22981
22994
23021
23030
23047
23062
23109
23157
23172
23173
23206
23299
23575
24758
24954
25020
28465
28868
28870
28872
28885
28887