mysql - 重写搜索查询

我的准系统搜索系统变得更加复杂，并且出现了许多奇怪的问题。如何重写此搜索查询，使其更准确，更有效

tbl_notes表仅具有course的ID，但为了将其添加到搜索中，我也使用内部查询对其进行了检查。这有很多问题，我在mysql中不太强，将不胜感激。

TBL_NOTES：

[id][user_id][note_title][note_title][topic][course][note]

TBL_COURSES：

  [id][user_id][course_name]

   SELECT * FROM `tbl_notes`
    WHERE `active` = '0'
    AND `valid_note` = '0'
    AND `user_id` = '33'
    AND `note` LIKE '%biology%'
    OR `topic` LIKE '%biology%'
    OR `note_title` LIKE '%biology%'
    OR `course` IN (SELECT `id` FROM tbl_courses WHERE `course_name` LIKE '%biology%' AND user_id = '33')
    ORDER BY id DESC LIMIT 0, 6

谢谢

最佳答案

我会这样写：

SELECT
  DISTINCT n.*
FROM
  `tbl_notes` n left join `tbl_courses` c
  on n.course = c.id
WHERE
  n.`user_id` = '33'
  AND n.`active` = '0'
  AND n.`valid_note` = '0'
  AND (n.`note` LIKE '%biology%'
       OR n.`topic` LIKE '%biology%'
       OR n.`note_title` LIKE '%biology%'
       OR c.`course_name` LIKE '%biology%')
ORDER BY n.id DESC LIMIT 0, 6

关于mysql - 重写搜索查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13373772/

Biology

mysql - 重写搜索查询