SELECT 
  v.variant_id, 
  v.product_id, 
  vd.variant, 
  vf.position, 
  vf.color 
FROM 
  cscart_product_features_values v 
  LEFT JOIN cscart_product_feature_variant_descriptions vd ON v.variant_id = vd.variant_id 
  AND vd.lang_code = 'en' 
  LEFT JOIN cscart_product_feature_variants vf ON v.variant_id = vf.variant_id 
WHERE 
  1 
  AND v.feature_id = 1383 
  AND v.product_id IN (
    '55536', '55535', '55534', '55533', 
    '55532', '55531', '55530', '55529', 
    '55528', '55527', '55526', '55525', 
    '55524', '55523', '55522', '55521', 
    '55520', '55295', '55537', '55538', 
    '55539', '55540', '55541', '55542', 
    '55543', '55544', '55545', '55546', 
    '55547', '55548', '55549', '55550', 
    '55551', '55552', '55553', '55554', 
    '55555', '55556', '55557', '55558', 
    '55559', '55560', '55561', '55562', 
    '55563', '55564', '55565', '55566', 
    '55567', '55568', '55569', '55570', 
    '55571', '55572'
  )

Query time 0.00212

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "49.39"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "v",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "idx_product_feature_variant_id",
          "used_key_parts": [
            "product_id",
            "feature_id"
          ],
          "key_length": "6",
          "rows_examined_per_scan": 54,
          "rows_produced_per_join": 54,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "6.19",
            "eval_cost": "5.40",
            "prefix_cost": "11.59",
            "data_read_per_join": "41K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "((`cscartdb`.`v`.`feature_id` = 1383) and (`cscartdb`.`v`.`product_id` in ('55536','55535','55534','55533','55532','55531','55530','55529','55528','55527','55526','55525','55524','55523','55522','55521','55520','55295','55537','55538','55539','55540','55541','55542','55543','55544','55545','55546','55547','55548','55549','55550','55551','55552','55553','55554','55555','55556','55557','55558','55559','55560','55561','55562','55563','55564','55565','55566','55567','55568','55569','55570','55571','55572')))"
        }
      },
      {
        "table": {
          "table_name": "vd",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "variant_id",
            "lang_code"
          ],
          "key_length": "9",
          "ref": [
            "cscartdb.v.variant_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 54,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "13.50",
            "eval_cost": "5.40",
            "prefix_cost": "30.49",
            "data_read_per_join": "163K"
          },
          "used_columns": [
            "variant_id",
            "variant",
            "lang_code"
          ]
        }
      },
      {
        "table": {
          "table_name": "vf",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "variant_id"
          ],
          "key_length": "3",
          "ref": [
            "cscartdb.v.variant_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 54,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "13.50",
            "eval_cost": "5.40",
            "prefix_cost": "49.39",
            "data_read_per_join": "61K"
          },
          "used_columns": [
            "variant_id",
            "color",
            "position"
          ]
        }
      }
    ]
  }
}