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 (
    '19233', '57260', '57261', '57262', 
    '57263', '57264', '57265', '57266', 
    '57267', '57268', '57269', '57270', 
    '57271', '57272', '57273', '57274', 
    '57275', '57276', '57277', '57278', 
    '57279', '57280', '57281', '57282', 
    '57283', '57284', '57285', '57286', 
    '57287', '57288', '57289', '57290', 
    '57291', '57292', '57293', '57294', 
    '57295', '57296', '57297', '57298', 
    '57299', '57300', '57301', '57302', 
    '57303', '57304', '57305', '57306', 
    '57307', '57308', '57309', '57310', 
    '57311', '57312', '57313', '57314', 
    '57315', '57316', '57317', '57318', 
    '57319', '57320', '57321', '57322', 
    '57323', '57324', '57325', '57326', 
    '57327', '57328', '57329', '57330', 
    '57331', '57332', '57333', '57334', 
    '57335', '57336', '57337', '57338', 
    '57339', '57340', '57341', '57342', 
    '57343', '57344', '57345', '57346', 
    '57347', '57348'
  )

Query time 0.00328

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "83.06"
    },
    "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": 91,
          "rows_produced_per_join": 91,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "10.26",
            "eval_cost": "9.10",
            "prefix_cost": "19.36",
            "data_read_per_join": "70K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "((`cscartdb`.`v`.`feature_id` = 345) and (`cscartdb`.`v`.`product_id` in ('19233','57260','57261','57262','57263','57264','57265','57266','57267','57268','57269','57270','57271','57272','57273','57274','57275','57276','57277','57278','57279','57280','57281','57282','57283','57284','57285','57286','57287','57288','57289','57290','57291','57292','57293','57294','57295','57296','57297','57298','57299','57300','57301','57302','57303','57304','57305','57306','57307','57308','57309','57310','57311','57312','57313','57314','57315','57316','57317','57318','57319','57320','57321','57322','57323','57324','57325','57326','57327','57328','57329','57330','57331','57332','57333','57334','57335','57336','57337','57338','57339','57340','57341','57342','57343','57344','57345','57346','57347','57348')))"
        }
      },
      {
        "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": 91,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "22.75",
            "eval_cost": "9.10",
            "prefix_cost": "51.21",
            "data_read_per_join": "275K"
          },
          "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": 91,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "22.75",
            "eval_cost": "9.10",
            "prefix_cost": "83.06",
            "data_read_per_join": "103K"
          },
          "used_columns": [
            "variant_id",
            "color",
            "position"
          ]
        }
      }
    ]
  }
}