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 = 345 
  AND v.product_id IN (
    '27261', '27265', '27264', '27263', 
    '27262', '27252', '27260', '27259', 
    '27258', '27257', '27256', '27255', 
    '27254', '27253', '27243', '27251', 
    '27250', '27249', '27248', '27247', 
    '27246', '27245', '27244', '27234', 
    '27242', '27241', '27240', '27239', 
    '27238', '27237', '27236', '27235', 
    '27216', '27233', '27232', '27231', 
    '27230', '27229', '27228', '27227', 
    '27226', '27225', '27224', '27223', 
    '27222', '27221', '27220', '27219', 
    '27218', '27217', '27207', '27215', 
    '27214', '27213', '27212', '27211', 
    '27210', '27209', '27208', '27198', 
    '27206', '27205', '27204', '27203', 
    '27202', '27201', '27200', '27199', 
    '27197', '27196', '27195', '27194', 
    '27193', '27192', '19105'
  )

Query time 0.00228

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` = 345) and (`cscartdb`.`v`.`product_id` in ('27261','27265','27264','27263','27262','27252','27260','27259','27258','27257','27256','27255','27254','27253','27243','27251','27250','27249','27248','27247','27246','27245','27244','27234','27242','27241','27240','27239','27238','27237','27236','27235','27216','27233','27232','27231','27230','27229','27228','27227','27226','27225','27224','27223','27222','27221','27220','27219','27218','27217','27207','27215','27214','27213','27212','27211','27210','27209','27208','27198','27206','27205','27204','27203','27202','27201','27200','27199','27197','27196','27195','27194','27193','27192','19105')))"
        }
      },
      {
        "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"
          ]
        }
      }
    ]
  }
}