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 (
    '18489', '55622', '55623', '55624', 
    '55625', '55626', '55627', '55628', 
    '55629', '55630', '55631', '55632', 
    '55633', '55634', '55635', '55636', 
    '55637', '55638', '55639', '55640', 
    '55641', '55642', '55643', '55644', 
    '55645', '55646', '55647', '55648', 
    '55649', '55650', '55651', '55652', 
    '55653', '55654', '55655', '55656', 
    '55657', '55658', '55659', '55660', 
    '55661', '55662', '55663', '55664', 
    '55665', '55666', '55667', '55668', 
    '55669', '55670', '55671', '55672', 
    '55673', '55674', '55675', '55676', 
    '55677', '55678', '55679', '55680', 
    '55681', '55682', '55683', '55684', 
    '55685', '55686', '55687', '55688', 
    '55689', '55690', '55691', '55692', 
    '55693', '55694', '55695', '55696', 
    '55697', '55698', '55699', '55700', 
    '55701', '55702', '55703', '55704', 
    '55705', '55706', '55707', '55708', 
    '55709', '55710'
  )

Query time 0.00299

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "83.97"
    },
    "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": 92,
          "rows_produced_per_join": 92,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "10.37",
            "eval_cost": "9.20",
            "prefix_cost": "19.57",
            "data_read_per_join": "71K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "((`cscartdb`.`v`.`feature_id` = 1392) and (`cscartdb`.`v`.`product_id` in ('18489','55622','55623','55624','55625','55626','55627','55628','55629','55630','55631','55632','55633','55634','55635','55636','55637','55638','55639','55640','55641','55642','55643','55644','55645','55646','55647','55648','55649','55650','55651','55652','55653','55654','55655','55656','55657','55658','55659','55660','55661','55662','55663','55664','55665','55666','55667','55668','55669','55670','55671','55672','55673','55674','55675','55676','55677','55678','55679','55680','55681','55682','55683','55684','55685','55686','55687','55688','55689','55690','55691','55692','55693','55694','55695','55696','55697','55698','55699','55700','55701','55702','55703','55704','55705','55706','55707','55708','55709','55710')))"
        }
      },
      {
        "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": 92,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "23.00",
            "eval_cost": "9.20",
            "prefix_cost": "51.77",
            "data_read_per_join": "278K"
          },
          "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": 92,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "23.00",
            "eval_cost": "9.20",
            "prefix_cost": "83.97",
            "data_read_per_join": "104K"
          },
          "used_columns": [
            "variant_id",
            "color",
            "position"
          ]
        }
      }
    ]
  }
}