SELECT 
  c.product_id AS cur_product_id, 
  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 = 'ja' 
  LEFT JOIN cscart_product_global_option_links as c ON c.option_id = a.option_id 
WHERE 
  c.product_id IN (
    115, 116, 117, 118, 119, 120, 121, 122, 
    123, 124, 125, 126, 127, 128, 129, 130, 
    131, 132, 133, 134, 135, 136, 137, 138, 
    139, 140, 141, 142, 143, 144, 145, 146, 
    147, 148, 149, 150, 151, 152, 153, 154, 
    155, 156, 157, 158, 159, 160, 161, 162, 
    163, 164, 165, 167, 168, 169, 170, 171, 
    172, 173, 174, 175, 176, 177, 178, 179, 
    180, 181, 182, 183, 184, 185, 186, 187, 
    188, 189, 190, 191, 1, 5, 7, 8, 9, 10, 11, 
    12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 
    22, 23, 24
  ) 
  AND a.product_id = 0 
  AND a.status = 'A' 
ORDER BY 
  a.position

Query time 0.00076

JSON explain

{
  "query_block": {
    "select_id": 1,
    "read_sorted_file": {
      "filesort": {
        "sort_key": "a.position",
        "table": {
          "table_name": "a",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "c_status"],
          "rows": 10,
          "filtered": 100,
          "attached_condition": "a.product_id = 0 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": ["smscrm_edev.a.option_id", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(b.lang_code = 'ja')"
    },
    "table": {
      "table_name": "c",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "product_id"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["option_id"],
      "ref": ["smscrm_edev.a.option_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "c.product_id in (115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,1,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)",
      "using_index": true
    }
  }
}