Sandbox

SELECT 
  a.*, 
  b.option_name, 
  b.internal_option_name, 
  b.option_text, 
  b.description, 
  b.inner_hint, 
  b.incorrect_message, 
  b.comment 
FROM 
  cscart_product_options as a 
  LEFT JOIN cscart_product_options_descriptions as b ON a.option_id = b.option_id 
  AND b.lang_code = 'en' 
WHERE 
  a.product_id IN (
    153378, 
    146250, 
    151676, 
    170573, 
    132911, 
    151528, 
    168300, 
    146008, 
    149505, 
    137270, 
    118463, 
    175108, 
    120360, 
    125752, 
    145670, 
    165520, 
    164728, 
    168291, 
    3459, 
    135232, 
    134222, 
    163348, 
    147116, 
    141352, 
    173196, 
    133367, 
    136278, 
    164224, 
    169761, 
    120371, 
    126781, 
    174454, 
    159721, 
    122698, 
    146691, 
    132361, 
    145504, 
    151584, 
    163029, 
    142300, 
    148407, 
    159680, 
    166851, 
    135515, 
    158366, 
    167558, 
    118856, 
    150294, 
    120895, 
    164939, 
    148060, 
    170707, 
    154297, 
    169846, 
    159381, 
    165473, 
    152983, 
    168016, 
    159560, 
    157441, 
    160602, 
    125438, 
    174205, 
    125076, 
    141675, 
    141823, 
    120656, 
    126863, 
    158696, 
    145139, 
    137529, 
    141871, 
    139050, 
    175166, 
    171127, 
    120496, 
    169901, 
    138735, 
    146824, 
    158471, 
    162470, 
    154893, 
    146279, 
    121442, 
    125787, 
    120264, 
    138776, 
    164451, 
    138985, 
    121657, 
    139237, 
    172341, 
    169627, 
    121067, 
    122178, 
    131028, 
    133688, 
    135620, 
    129139, 
    156331, 
    177744, 
    160951, 
    160534, 
    171706, 
    128069, 
    163441, 
    131982, 
    170708, 
    136526, 
    152889, 
    146907, 
    169481, 
    172066, 
    165323, 
    120928, 
    143092, 
    160218, 
    146004, 
    169267, 
    151452, 
    165666, 
    149523, 
    131594, 
    129845, 
    133281, 
    148051, 
    163097, 
    144228
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position

Query time 0.00085

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.037110106,
    "nested_loop": [
      {
        "read_sorted_file": {
          "filesort": {
            "sort_key": "a.position",
            "table": {
              "table_name": "a",
              "access_type": "ALL",
              "possible_keys": ["c_status"],
              "loops": 1,
              "rows": 15,
              "cost": 0.0132998,
              "filtered": 100,
              "attached_condition": "a.product_id in (153378,146250,151676,170573,132911,151528,168300,146008,149505,137270,118463,175108,120360,125752,145670,165520,164728,168291,3459,135232,134222,163348,147116,141352,173196,133367,136278,164224,169761,120371,126781,174454,159721,122698,146691,132361,145504,151584,163029,142300,148407,159680,166851,135515,158366,167558,118856,150294,120895,164939,148060,170707,154297,169846,159381,165473,152983,168016,159560,157441,160602,125438,174205,125076,141675,141823,120656,126863,158696,145139,137529,141871,139050,175166,171127,120496,169901,138735,146824,158471,162470,154893,146279,121442,125787,120264,138776,164451,138985,121657,139237,172341,169627,121067,122178,131028,133688,135620,129139,156331,177744,160951,160534,171706,128069,163441,131982,170708,136526,152889,146907,169481,172066,165323,120928,143092,160218,146004,169267,151452,165666,149523,131594,129845,133281,148051,163097,144228) and a.`status` = 'A'"
            }
          }
        }
      },
      {
        "table": {
          "table_name": "b",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["option_id", "lang_code"],
          "ref": ["u985510652_ecartify_abhi.a.option_id", "const"],
          "loops": 15,
          "rows": 1,
          "cost": 0.0142358,
          "filtered": 100,
          "attached_condition": "trigcond(b.lang_code = 'en')"
        }
      }
    ]
  }
}