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 = 348 
  AND v.product_id IN (
    '18638', '57377', '57378', '57379', 
    '57380', '57381', '57382', '57383', 
    '57384', '57385', '57386', '57387', 
    '57388', '57389', '57390', '57391', 
    '57392', '57393', '57394', '57395', 
    '57396', '57397', '57398', '57399', 
    '57400', '57401', '57402', '57403', 
    '57404', '57405', '57406', '57407', 
    '57408', '57409', '57410', '57411', 
    '57412', '57413', '57414', '57415', 
    '57416', '57417', '57418', '57419', 
    '57420', '57421', '57422', '57423', 
    '57424', '57425', '57426', '57427', 
    '57428', '57429', '57430', '57431', 
    '57432', '57433', '57434', '57435', 
    '57436', '57437', '57438', '57439', 
    '57440', '57441', '57442', '57443', 
    '57444', '57445', '57446', '57447', 
    '57448', '57449', '57450'
  )

Query time 0.00272

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "68.50"
    },
    "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": 75,
          "rows_produced_per_join": 75,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "8.50",
            "eval_cost": "7.50",
            "prefix_cost": "16.00",
            "data_read_per_join": "58K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "((`cscartdb`.`v`.`feature_id` = 348) and (`cscartdb`.`v`.`product_id` in ('18638','57377','57378','57379','57380','57381','57382','57383','57384','57385','57386','57387','57388','57389','57390','57391','57392','57393','57394','57395','57396','57397','57398','57399','57400','57401','57402','57403','57404','57405','57406','57407','57408','57409','57410','57411','57412','57413','57414','57415','57416','57417','57418','57419','57420','57421','57422','57423','57424','57425','57426','57427','57428','57429','57430','57431','57432','57433','57434','57435','57436','57437','57438','57439','57440','57441','57442','57443','57444','57445','57446','57447','57448','57449','57450')))"
        }
      },
      {
        "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": 75,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "18.75",
            "eval_cost": "7.50",
            "prefix_cost": "42.25",
            "data_read_per_join": "226K"
          },
          "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": 75,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "18.75",
            "eval_cost": "7.50",
            "prefix_cost": "68.50",
            "data_read_per_join": "85K"
          },
          "used_columns": [
            "variant_id",
            "color",
            "position"
          ]
        }
      }
    ]
  }
}