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 = 'ar' 
WHERE 
  a.product_id IN (
    2187, 
    2244, 
    2172, 
    2171, 
    1668, 
    8586, 
    8372, 
    2222, 
    2175, 
    2228, 
    2220, 
    2177, 
    2230, 
    2226, 
    1667, 
    2221, 
    2179, 
    1666, 
    8240, 
    2183, 
    8813, 
    2176, 
    8433, 
    2224, 
    2195, 
    2225, 
    8514, 
    2189, 
    2196, 
    2186, 
    2180, 
    2178, 
    2191, 
    2170, 
    2181, 
    2190, 
    2185, 
    2198, 
    2182, 
    2188, 
    2173, 
    8784, 
    2193, 
    2184, 
    2197, 
    2174, 
    2199, 
    2219, 
    2223, 
    2194, 
    2243, 
    2200, 
    2229, 
    8789, 
    8695, 
    8766, 
    8802, 
    8362, 
    8573, 
    8696, 
    8243, 
    8617, 
    8601, 
    8375, 
    8570, 
    8812, 
    8536, 
    8604, 
    8589, 
    8699, 
    8590, 
    8612, 
    8800, 
    8702, 
    8576, 
    8701, 
    8464, 
    8469, 
    8577, 
    8708, 
    8703, 
    8783, 
    8804, 
    8275, 
    8569, 
    8724, 
    8445, 
    8470, 
    8435, 
    8801, 
    8805, 
    8811, 
    8815, 
    8441, 
    8446, 
    8543, 
    8245, 
    8440, 
    8519, 
    8593, 
    8595, 
    8432, 
    8444, 
    8471, 
    8594, 
    8472, 
    8457, 
    8806, 
    8828, 
    8830, 
    8575, 
    8269, 
    8466, 
    8363, 
    8459, 
    8460, 
    8603, 
    8581, 
    8709, 
    8438, 
    8799, 
    8831, 
    8241, 
    8272, 
    8273, 
    8276, 
    8365, 
    8382, 
    8538, 
    8572, 
    8578, 
    8580, 
    8585, 
    8597, 
    8614, 
    8615, 
    8729, 
    8730, 
    8757, 
    8797, 
    8810, 
    8814, 
    8829, 
    8242, 
    8244, 
    8277, 
    8280, 
    8373, 
    8377, 
    8403, 
    8431, 
    8434, 
    8443, 
    8517, 
    8540, 
    8574, 
    8587, 
    8596, 
    8600, 
    8605
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position

Query time 0.00092

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19.37"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "13.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "a",
            "access_type": "ALL",
            "possible_keys": [
              "c_status"
            ],
            "rows_examined_per_scan": 13,
            "rows_produced_per_join": 13,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.52",
              "eval_cost": "1.30",
              "prefix_cost": "1.82",
              "data_read_per_join": "29K"
            },
            "used_columns": [
              "option_id",
              "product_id",
              "company_id",
              "option_type",
              "regexp",
              "required",
              "multiupload",
              "allowed_extensions",
              "max_file_size",
              "missing_variants_handling",
              "status",
              "position",
              "value"
            ],
            "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`a`.`product_id` in (2187,2244,2172,2171,1668,8586,8372,2222,2175,2228,2220,2177,2230,2226,1667,2221,2179,1666,8240,2183,8813,2176,8433,2224,2195,2225,8514,2189,2196,2186,2180,2178,2191,2170,2181,2190,2185,2198,2182,2188,2173,8784,2193,2184,2197,2174,2199,2219,2223,2194,2243,2200,2229,8789,8695,8766,8802,8362,8573,8696,8243,8617,8601,8375,8570,8812,8536,8604,8589,8699,8590,8612,8800,8702,8576,8701,8464,8469,8577,8708,8703,8783,8804,8275,8569,8724,8445,8470,8435,8801,8805,8811,8815,8441,8446,8543,8245,8440,8519,8593,8595,8432,8444,8471,8594,8472,8457,8806,8828,8830,8575,8269,8466,8363,8459,8460,8603,8581,8709,8438,8799,8831,8241,8272,8273,8276,8365,8382,8538,8572,8578,8580,8585,8597,8614,8615,8729,8730,8757,8797,8810,8814,8829,8242,8244,8277,8280,8373,8377,8403,8431,8434,8443,8517,8540,8574,8587,8596,8600,8605)) and (`softwarepirmam_hewadelivard_cscart_4`.`a`.`status` = 'A'))"
          }
        },
        {
          "table": {
            "table_name": "b",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "option_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.a.option_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 13,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "3.25",
              "eval_cost": "1.30",
              "prefix_cost": "6.37",
              "data_read_per_join": "44K"
            },
            "used_columns": [
              "option_id",
              "lang_code",
              "option_name",
              "internal_option_name",
              "option_text",
              "description",
              "comment",
              "inner_hint",
              "incorrect_message"
            ]
          }
        }
      ]
    }
  }
}