SELECT 
  o.user_id 
FROM 
  tmall_orders AS o 
  INNER JOIN tmall_order_details AS od ON o.order_id = od.order_id 
  AND od.product_id = 242 
WHERE 
  o.user_id IN (1) 
  AND o.status IN ('P', 'C')

Query time 0.00981

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.004633796,
    "nested_loop": [
      {
        "table": {
          "table_name": "o",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "user_id", "status"],
          "key": "user_id",
          "key_length": "3",
          "used_key_parts": ["user_id"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.002024411,
          "filtered": 75,
          "attached_condition": "o.`status` in ('P','C')"
        }
      },
      {
        "table": {
          "table_name": "od",
          "access_type": "ref",
          "possible_keys": ["o_k"],
          "key": "o_k",
          "key_length": "6",
          "used_key_parts": ["order_id", "product_id"],
          "ref": ["tmall.o.order_id", "const"],
          "loops": 1,
          "rows": 13,
          "cost": 0.002609385,
          "filtered": 100,
          "using_index": true
        }
      }
    ]
  }
}