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 = 'en' 
  LEFT JOIN cscart_product_global_option_links as c ON c.option_id = a.option_id 
WHERE 
  c.product_id IN (
    98737, 98750, 98751, 98752, 98753, 98754, 
    98755, 98756, 98757, 99509, 99510, 
    99513, 99514, 99517, 99518, 99653, 
    99654, 99655, 99656, 99657, 99658, 
    99659, 99660, 99661, 99662, 99663, 
    99664, 99665, 99666, 99667, 99668, 
    100504, 100505, 100506, 100507, 100508, 
    100509, 100510, 100511, 100512, 100513, 
    100514, 100515, 100516, 100517, 100518, 
    100519, 100520, 100521, 100522, 100523, 
    100524, 100525, 100526, 100527, 100528, 
    100529, 100530, 100531, 100532, 100533, 
    100534, 100535, 100642, 100644, 100646, 
    100648, 100789, 100790, 100791, 100792, 
    100793, 100794, 100795, 100796, 100797, 
    100798, 100799, 100800, 100801, 100802, 
    100803, 100804, 100805, 100806, 100807, 
    100808, 100809, 100810, 100811, 100812, 
    100813, 100814, 100815, 100816, 100817
  ) 
  AND a.product_id = 0 
  AND a.status = 'A' 
ORDER BY 
  a.position

Query time 0.00083

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.05"
    },
    "ordering_operation": {
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "a",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "c_status"
            ],
            "key": "c_status",
            "used_key_parts": [
              "product_id",
              "status"
            ],
            "key_length": "6",
            "ref": [
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "0.10",
              "prefix_cost": "0.35",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "option_id",
              "product_id",
              "company_id",
              "option_type",
              "regexp",
              "required",
              "multiupload",
              "allowed_extensions",
              "max_file_size",
              "missing_variants_handling",
              "status",
              "position",
              "value"
            ]
          }
        },
        {
          "table": {
            "table_name": "c",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "product_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "option_id"
            ],
            "key_length": "3",
            "ref": [
              "nuie_scalesta_net.a.option_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "0.10",
              "prefix_cost": "0.70",
              "data_read_per_join": "8"
            },
            "used_columns": [
              "option_id",
              "product_id"
            ],
            "attached_condition": "(`nuie_scalesta_net`.`c`.`product_id` in (98737,98750,98751,98752,98753,98754,98755,98756,98757,99509,99510,99513,99514,99517,99518,99653,99654,99655,99656,99657,99658,99659,99660,99661,99662,99663,99664,99665,99666,99667,99668,100504,100505,100506,100507,100508,100509,100510,100511,100512,100513,100514,100515,100516,100517,100518,100519,100520,100521,100522,100523,100524,100525,100526,100527,100528,100529,100530,100531,100532,100533,100534,100535,100642,100644,100646,100648,100789,100790,100791,100792,100793,100794,100795,100796,100797,100798,100799,100800,100801,100802,100803,100804,100805,100806,100807,100808,100809,100810,100811,100812,100813,100814,100815,100816,100817))"
          }
        },
        {
          "table": {
            "table_name": "b",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "option_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "nuie_scalesta_net.a.option_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "0.10",
              "prefix_cost": "1.05",
              "data_read_per_join": "3K"
            },
            "used_columns": [
              "option_id",
              "lang_code",
              "option_name",
              "internal_option_name",
              "option_text",
              "description",
              "comment",
              "inner_hint",
              "incorrect_message"
            ]
          }
        }
      ]
    }
  }
}