SELECT 
  tmall_ab__product_sets.set_id, 
  tmall_ab__product_sets.product_id, 
  tmall_ab__product_sets.status, 
  tmall_ab__product_sets.pos, 
  tmall_ab__product_sets_descriptions.set, 
  GROUP_CONCAT(
    tmall_ab__product_sets_products.product_id
  ) as products 
FROM 
  tmall_ab__product_sets 
  LEFT JOIN tmall_ab__product_sets_descriptions ON tmall_ab__product_sets_descriptions.set_id = tmall_ab__product_sets.set_id 
  AND tmall_ab__product_sets_descriptions.lang_code = 'en' 
  LEFT JOIN tmall_ab__product_sets_products ON tmall_ab__product_sets_products.set_id = tmall_ab__product_sets.set_id 
WHERE 
  tmall_ab__product_sets.product_id = 47 
  AND tmall_ab__product_sets.status = "A" 
GROUP BY 
  tmall_ab__product_sets.set_id 
ORDER BY 
  tmall_ab__product_sets.pos ASC

Query time 0.00846

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.00695597,
    "filesort": {
      "sort_key": "tmall_ab__product_sets.pos",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "tmall_ab__product_sets",
              "access_type": "ref",
              "possible_keys": ["status", "product_id"],
              "key": "status",
              "key_length": "3",
              "used_key_parts": ["status"],
              "ref": ["const"],
              "loops": 1,
              "rows": 1,
              "cost": 0.00345856,
              "filtered": 100,
              "index_condition": "tmall_ab__product_sets.`status` = 'A'",
              "attached_condition": "tmall_ab__product_sets.`status` <=> 'A' and tmall_ab__product_sets.product_id = 47"
            }
          },
          {
            "table": {
              "table_name": "tmall_ab__product_sets_descriptions",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "10",
              "used_key_parts": ["set_id", "lang_code"],
              "ref": ["tmall.tmall_ab__product_sets.set_id", "const"],
              "loops": 1,
              "rows": 1,
              "cost": 0.00171364,
              "filtered": 100,
              "attached_condition": "trigcond(tmall_ab__product_sets_descriptions.lang_code = 'en')"
            }
          },
          {
            "table": {
              "table_name": "tmall_ab__product_sets_products",
              "access_type": "ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["set_id"],
              "ref": ["tmall.tmall_ab__product_sets.set_id"],
              "loops": 1,
              "rows": 1,
              "cost": 0.00178377,
              "filtered": 100
            }
          }
        ]
      }
    }
  }
}