my below query is very slow please tell me what is the reason

SELECT cm.course_id, xu.full_path, xu.file_name, xu.file_id, xu.virtual_server
FROM cms_doc.xyf_urls xu
LEFT JOIN bb_bb60.course_main cm ON xu.full_path LIKE '/courses/' ||
cm.course_id OR xu.full_path LIKE '/courses/' || cm.course_id || '%'
WHERE xu.full_path NOT LIKE '/orgs%'
AND xu.full_path NOT LIKE '/institution%'
AND xu.full_path NOT LIKE '/library%'
AN开发者_如何学CD xu.full_path NOT LIKE '/internal%'
AND xu.full_path NOT LIKE '/user%'
ORDER BY cm.course_id 

---

It's slow because all of the all the LIKEs, particularly as part of the JOIN.

---

First, make sure that there is an index on xu.full_path.

Second, one of the two OR conditions seems redundant. Try using this:

SELECT cm.course_id, xu.full_path, xu.file_name, xu.file_id, xu.virtual_server
FROM cms_doc.xyf_urls xu
  LEFT JOIN bb_bb60.course_main cm
    ON xu.full_path LIKE '/courses/' || cm.course_id || '%'
WHERE xu.full_path NOT LIKE '/orgs%'
  AND xu.full_path NOT LIKE '/institution%'
  AND xu.full_path NOT LIKE '/library%'
  AND xu.full_path NOT LIKE '/internal%'
  AND xu.full_path NOT LIKE '/user%'
ORDER BY cm.course_id