SELECT 
  q_product_feature_variants.variant_id, 
  q_product_feature_variants.position, 
  q_product_feature_variants.color, 
  q_product_feature_variant_descriptions.variant 
FROM 
  q_product_feature_variants 
  LEFT JOIN q_product_feature_variant_descriptions ON q_product_feature_variants.variant_id = q_product_feature_variant_descriptions.variant_id 
  AND q_product_feature_variant_descriptions.lang_code = 'en' 
WHERE 
  q_product_feature_variants.variant_id IN (
    7, 
    8, 
    10, 
    16, 
    27, 
    28, 
    66, 
    69, 
    70, 
    71, 
    72, 
    73, 
    80, 
    81, 
    297, 
    5779, 
    5785, 
    5839, 
    5841, 
    5842, 
    5862, 
    5943, 
    5957, 
    5958, 
    5995, 
    6026, 
    6027, 
    6028, 
    6098, 
    6155, 
    6643, 
    6672, 
    6714, 
    6731, 
    6735, 
    7558, 
    7559, 
    7562, 
    7576, 
    7603, 
    8163, 
    8167, 
    8171, 
    8188, 
    8190, 
    8191, 
    8193, 
    8194, 
    8195, 
    8196, 
    8197, 
    8198, 
    8199, 
    8200, 
    8201, 
    8202, 
    8203, 
    8204, 
    8205, 
    8207, 
    8209, 
    8210, 
    8211, 
    8212, 
    8213, 
    8214, 
    8215, 
    8216, 
    8217, 
    8218, 
    8220, 
    8221, 
    8222, 
    8223, 
    8225, 
    8226, 
    8227, 
    8229, 
    8230, 
    8231, 
    8232, 
    8233, 
    8234, 
    8235, 
    8238, 
    8239, 
    8253, 
    8254, 
    8287, 
    8289, 
    8291, 
    8292, 
    8294, 
    8298, 
    8300, 
    8347, 
    8350, 
    8351, 
    8353, 
    8354, 
    8355, 
    8357, 
    8358, 
    8361, 
    8366, 
    8370, 
    8374, 
    8377, 
    8378, 
    8379, 
    8382, 
    8387, 
    8388, 
    8389, 
    8390, 
    8391, 
    8394, 
    8396, 
    8397, 
    8398, 
    8399, 
    8400, 
    8403, 
    8404, 
    8407, 
    8408, 
    8409, 
    8410, 
    8414, 
    8416, 
    8419, 
    8420
  ) 
ORDER BY 
  q_product_feature_variants.position ASC, 
  q_product_feature_variant_descriptions.variant ASC

Query time 0.00183

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "475.21"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "q_product_feature_variants",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 132,
            "rows_produced_per_join": 132,
            "filtered": "100.00",
            "index_condition": "(`portal`.`q_product_feature_variants`.`variant_id` in (7,8,10,16,27,28,66,69,70,71,72,73,80,81,297,5779,5785,5839,5841,5842,5862,5943,5957,5958,5995,6026,6027,6028,6098,6155,6643,6672,6714,6731,6735,7558,7559,7562,7576,7603,8163,8167,8171,8188,8190,8191,8193,8194,8195,8196,8197,8198,8199,8200,8201,8202,8203,8204,8205,8207,8209,8210,8211,8212,8213,8214,8215,8216,8217,8218,8220,8221,8222,8223,8225,8226,8227,8229,8230,8231,8232,8233,8234,8235,8238,8239,8253,8254,8287,8289,8291,8292,8294,8298,8300,8347,8350,8351,8353,8354,8355,8357,8358,8361,8366,8370,8374,8377,8378,8379,8382,8387,8388,8389,8390,8391,8394,8396,8397,8398,8399,8400,8403,8404,8407,8408,8409,8410,8414,8416,8419,8420))",
            "cost_info": {
              "read_cost": "290.41",
              "eval_cost": "26.40",
              "prefix_cost": "316.81",
              "data_read_per_join": "150K"
            },
            "used_columns": [
              "variant_id",
              "color",
              "position"
            ]
          }
        },
        {
          "table": {
            "table_name": "q_product_feature_variant_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "portal.q_product_feature_variants.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 132,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "132.00",
              "eval_cost": "26.40",
              "prefix_cost": "475.21",
              "data_read_per_join": "399K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}