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, 
    9, 
    10, 
    13, 
    14, 
    15, 
    16, 
    27, 
    28, 
    66, 
    67, 
    69, 
    70, 
    71, 
    72, 
    73, 
    75, 
    76, 
    78, 
    79, 
    80, 
    81, 
    297, 
    5779, 
    5781, 
    5785, 
    5839, 
    5841, 
    5842, 
    5843, 
    5844, 
    5918, 
    5957, 
    6026, 
    6087, 
    6097, 
    6098, 
    6158, 
    6180, 
    6437, 
    6643, 
    6714, 
    6728, 
    7557, 
    7559, 
    7603, 
    7651, 
    8159, 
    8163, 
    8189, 
    8190, 
    8191, 
    8192, 
    8193, 
    8197, 
    8200, 
    8203, 
    8204, 
    8206, 
    8208, 
    8210, 
    8211, 
    8212, 
    8215, 
    8219, 
    8222, 
    8224, 
    8225, 
    8226, 
    8228, 
    8230, 
    8231, 
    8235, 
    8236, 
    8237, 
    8241, 
    8246, 
    8248, 
    8253, 
    8254, 
    8284, 
    8286, 
    8288, 
    8291, 
    8293, 
    8295, 
    8296, 
    8297, 
    8298, 
    8299, 
    8301, 
    8302, 
    8304, 
    8305, 
    8306, 
    8307, 
    8346, 
    8347, 
    8348, 
    8352, 
    8353, 
    8354, 
    8358, 
    8359, 
    8362, 
    8363, 
    8365, 
    8367, 
    8368, 
    8369, 
    8370, 
    8371, 
    8380, 
    8383, 
    8384, 
    8385, 
    8386, 
    8389, 
    8390, 
    8391, 
    8393, 
    8395, 
    8396, 
    8399, 
    8405, 
    8406, 
    8407, 
    8411, 
    8412, 
    8413, 
    8414, 
    8415, 
    8416, 
    8465
  ) 
ORDER BY 
  q_product_feature_variants.position ASC, 
  q_product_feature_variant_descriptions.variant ASC

Query time 0.00221

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "486.01"
    },
    "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": 135,
            "rows_produced_per_join": 135,
            "filtered": "100.00",
            "index_condition": "(`portal`.`q_product_feature_variants`.`variant_id` in (7,8,9,10,13,14,15,16,27,28,66,67,69,70,71,72,73,75,76,78,79,80,81,297,5779,5781,5785,5839,5841,5842,5843,5844,5918,5957,6026,6087,6097,6098,6158,6180,6437,6643,6714,6728,7557,7559,7603,7651,8159,8163,8189,8190,8191,8192,8193,8197,8200,8203,8204,8206,8208,8210,8211,8212,8215,8219,8222,8224,8225,8226,8228,8230,8231,8235,8236,8237,8241,8246,8248,8253,8254,8284,8286,8288,8291,8293,8295,8296,8297,8298,8299,8301,8302,8304,8305,8306,8307,8346,8347,8348,8352,8353,8354,8358,8359,8362,8363,8365,8367,8368,8369,8370,8371,8380,8383,8384,8385,8386,8389,8390,8391,8393,8395,8396,8399,8405,8406,8407,8411,8412,8413,8414,8415,8416,8465))",
            "cost_info": {
              "read_cost": "297.01",
              "eval_cost": "27.00",
              "prefix_cost": "324.01",
              "data_read_per_join": "153K"
            },
            "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": 135,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "135.00",
              "eval_cost": "27.00",
              "prefix_cost": "486.01",
              "data_read_per_join": "408K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}