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 = 592 
      AND p.status = 'A'
  )

Query time 0.00173

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
6112
6113
6114
6115
6116
6117
6118
6119
6120
6121
6122
6123
6124
14302
16111
16112
17234
20693
21424
22039
22569
22626
22628
23182
23185
23188
23250
23254
23377
23407
26016
26629
26827
27286
33001
7930
7931
7955
7956
7960
7961
7962
7963
7965
7967
7968
7969
7970
7971
7973
7975
7976
7977
7978
7979
7980
7981
7982
7983
7984
7985
7986
7987
7988
7989
7990
7991
7992
7993
7994
7995
7996
7997
7998
7999
8000
8001
8002
8003
8004
8005
8006
8007
8008
8009
8010
8011
8012
8013
8014
8015
8016
8017
8018
8019
8020
8021
8022
8023
8024
8025
8026
8027
8028
8029
8030
8031
8032
8033
8034
8035
8036
8037
8038
8046
8047
8048
8049
8050
8051
8052
8053
8054
8055
8056
8057
8058
8059
8060
8068
8069
8070
8071
8072
8073
8074
8075
8076
8077
8078
8079
8080
8081
8082
8083
8084
8085
8086
8090
8091
8092
8093
8094
8095
8096
8097
8098
8099
8100
8101
8102
8103
8104
8105
8106
8107
8108
8109
8110
8111
8112
8113
8114
8115
8116
8117
8118
8119
8120
8121
8122
8123
8124
8125
8126
8127
8157
8552
8553
8554
8555
8580
17143
19022
20690
21445
21525
21983
22135
22285
22776
22788
22798
22851
22871
22872
22878
23123
23193
23315
23404
23414
23792
8158
9337
28224
14051
14067
17191
22035
22179
22732
22735
22755
22958
23115
23148
23232
23544
24274
27288
28365
28367
28909
28911
28913
28915
28921
28923
33193
9079
9080
16695
17114
17209
19021
19028
23056
24271