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 = 1392 
  AND v.product_id IN (
    '18825', '57470', '57471', '57472', 
    '57473', '57474', '57475', '57476', 
    '57477', '57478', '57479', '57480', 
    '57481', '57482', '57483', '57484', 
    '57485', '57486', '57487', '57488', 
    '57489', '57490', '57491', '57492', 
    '57493', '57494', '57495', '57496', 
    '57497', '57498', '57499', '57500', 
    '57501', '57502', '57503', '57504', 
    '57505', '57506', '57507', '57508', 
    '57509', '57510', '57511', '57512', 
    '57513', '57514', '57515', '57516', 
    '57517', '57518', '57519', '57520', 
    '57521', '57522', '57523', '57524', 
    '57525', '57526', '57527', '57528', 
    '57529', '57530', '57531', '57532', 
    '57533', '57534', '57535', '57536', 
    '57537', '57538', '57539', '57540', 
    '57541', '57542', '57543', '57544', 
    '57545', '57546', '57547', '57548', 
    '57549', '57550', '57551', '57552', 
    '57553', '57554', '57555', '57556', 
    '57557', '57558'
  )

Query time 0.00314

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "82.15"
    },
    "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": 90,
          "rows_produced_per_join": 90,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "10.15",
            "eval_cost": "9.00",
            "prefix_cost": "19.15",
            "data_read_per_join": "69K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "((`cscartdb`.`v`.`feature_id` = 1392) and (`cscartdb`.`v`.`product_id` in ('18825','57470','57471','57472','57473','57474','57475','57476','57477','57478','57479','57480','57481','57482','57483','57484','57485','57486','57487','57488','57489','57490','57491','57492','57493','57494','57495','57496','57497','57498','57499','57500','57501','57502','57503','57504','57505','57506','57507','57508','57509','57510','57511','57512','57513','57514','57515','57516','57517','57518','57519','57520','57521','57522','57523','57524','57525','57526','57527','57528','57529','57530','57531','57532','57533','57534','57535','57536','57537','57538','57539','57540','57541','57542','57543','57544','57545','57546','57547','57548','57549','57550','57551','57552','57553','57554','57555','57556','57557','57558')))"
        }
      },
      {
        "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": 90,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "22.50",
            "eval_cost": "9.00",
            "prefix_cost": "50.65",
            "data_read_per_join": "272K"
          },
          "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": 90,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "22.50",
            "eval_cost": "9.00",
            "prefix_cost": "82.15",
            "data_read_per_join": "102K"
          },
          "used_columns": [
            "variant_id",
            "color",
            "position"
          ]
        }
      }
    ]
  }
}