SELECT 
  mdi.mdi_id, 
  mdi.tag, 
  mdi.is_faq, 
  mdi.is_open, 
  mdi.status, 
  mdi.position, 
  mdi.item_type, 
  mdi.filling_type, 
  mdi.filling_data, 
  mdi.position_on_page, 
  mdid.lang_code, 
  mdid.title, 
  mdid.description 
FROM 
  cscart_ab__md_items as mdi 
  LEFT JOIN cscart_ab__md_item_settings item_settings ON mdi.mdi_id = item_settings.item_id 
  AND item_settings.object_type = 'category' 
  LEFT JOIN cscart_ab__md_items_links global_links_include ON global_links_include.item_id = mdi.mdi_id 
  AND global_links_include.object_type = 'category' 
  AND (
    global_links_include.object_id = 549 
    AND global_links_include.link_type = 'I'
  ) 
  LEFT JOIN cscart_ab__md_items_links global_links_exclude ON global_links_exclude.item_id = mdi.mdi_id 
  AND global_links_exclude.object_type = 'category' 
  AND (
    global_links_exclude.object_id = 549 
    AND global_links_exclude.link_type = 'E'
  ) 
  INNER JOIN cscart_ab__md_item_descriptions AS mdid ON (
    mdi.mdi_id = mdid.mdi_id 
    AND mdid.lang_code = 'en' 
    AND IF(
      mdi.item_type = 'elements', 
      TRIM(mdid.description) != '', 
      TRIM(mdid.title) != ''
    )
  ) 
WHERE 
  1 
  AND item_settings.status = 'A' 
  AND (
    item_settings.is_global = 'Y' 
    OR global_links_include.object_id is not null
  ) 
  and global_links_exclude.object_id is null 
  AND mdi.status = 'A' 
ORDER BY 
  mdi.status asc, 
  mdi.position asc, 
  mdid.title asc

Query time 0.00062

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "mdi.position, mdid.title",
      "temporary_table": {
        "table": {
          "table_name": "mdi",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "mdi.`status` = 'A'"
        },
        "table": {
          "table_name": "item_settings",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "156",
          "used_key_parts": ["item_id", "object_type"],
          "ref": ["ymonline_livedata.mdi.mdi_id", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "item_settings.`status` = 'A' and mdi.mdi_id = item_settings.item_id and item_settings.object_type = 'category'"
        },
        "table": {
          "table_name": "global_links_include",
          "access_type": "ref",
          "possible_keys": ["object_id", "item_id"],
          "key": "object_id",
          "key_length": "156",
          "used_key_parts": ["object_id", "object_type"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(item_settings.is_global = 'Y' or global_links_include.object_id is not null) and trigcond(global_links_include.item_id = mdi.mdi_id and global_links_include.object_type = 'category' and global_links_include.link_type = 'I')"
        },
        "table": {
          "table_name": "global_links_exclude",
          "access_type": "ref",
          "possible_keys": ["object_id", "item_id"],
          "key": "object_id",
          "key_length": "156",
          "used_key_parts": ["object_id", "object_type"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(global_links_exclude.object_id is null) and trigcond(global_links_exclude.item_id = mdi.mdi_id and global_links_exclude.object_type = 'category' and global_links_exclude.link_type = 'E')",
          "not_exists": true
        },
        "table": {
          "table_name": "mdid",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "10",
          "used_key_parts": ["mdi_id", "lang_code"],
          "ref": ["ymonline_livedata.mdi.mdi_id", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "mdid.lang_code = 'en' and if(mdi.item_type = 'elements',trim(mdid.description) <> '',trim(mdid.title) <> '')"
        }
      }
    }
  }
}