mysql - 谁能制定出更优化的 SQL 解决方案？

Question

我担心这有点贵。

另外，我很快就会为标签实现一个标准化系统，这样就会有额外的连接。

最重要的是，我有 4 个表(tbl_videos、tbl_articles、tbl_galleries 和 tbl_users)，我想显示每个表的三个结果，因此只需按一下“搜索”就必须运行查询四次。

SELECT *, 
(
(CASE WHEN `description` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
(CASE WHEN `description` LIKE '%london%' THEN 1 ELSE 0 END) + 
(CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
(CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END) +

(CASE WHEN `title` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
(CASE WHEN `title` LIKE '%london%' THEN 1 ELSE 0 END) + 
(CASE WHEN `title` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
(CASE WHEN `title` LIKE '%dog%' THEN 1 ELSE 0 END) +

(CASE WHEN `tags` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
(CASE WHEN `tags` LIKE '%london%' THEN 1 ELSE 0 END) + 
(CASE WHEN `tags` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
(CASE WHEN `tags` LIKE '%dog%' THEN 1 ELSE 0 END)

) AS relevance
FROM `table`
WHERE `description` LIKE '%hotel%'
  OR `description` LIKE '%london%'
  OR `description` LIKE '%lazy%'
  OR `description` LIKE '%dog%' 
  OR `title` LIKE '%hotel%'
  OR `title` LIKE '%london%'
  OR `title` LIKE '%lazy%'
  OR `title` LIKE '%dog%'
  OR `tags` LIKE '%hotel%'
  OR `tags` LIKE '%london%'
  OR `tags` LIKE '%lazy%'
  OR `tags` LIKE '%dog%'
ORDER BY relevance DESC
LIMIT 0 , 3;

Answer 1

是的，这可能会非常消耗资源，但听起来在提交查询之前您前面有一个业务层。

考虑一下您想要实现的目标，以及您自己可以做什么，解析搜索词，甚至为用户提供单独的描述、标题和标签字段，并适本地构建查询，以更直接地搜索查询，而不是有效地说，“我在该表的列中的某处得到了一些与此搜索词非常相似的数据”。

一旦你确定了你的业务逻辑(如果上面的查询是如何结束的，尽管我对此表示怀疑，那么就这样吧)，那么你绝对应该通过解释计划运行查询，看看你可能会在哪里找到答案一些索引来帮助数据库运行。

编辑:

好的，这个建议怎么样

select matched_val, relevance from (
    select description as matched_val, count(*) as relevance
    from table 
    where description like '%hotel%'
    or description like '%london%'
    or description like '%lazy%'
    or description like '%dog%'
    group by description

    union all

    select title as matched_val, count(*) as relevance
    from table 
    where title like '%hotel%'
    or title like '%london%'
    or title like '%lazy%'
    or title like '%dog%'
    group by title

    union all

    select tags as matched_val, count(*) as relevance
    from table 
    where tags like '%hotel%'
    or tags like '%london%'
    or tags like '%lazy%'
    or tags like '%dog%'
    group by tags
) as a
order by a.relevance desc
LIMIT 0 , 3

这至少意味着你只需要做一次类似的匹配，而不是在谓词和 switch 语句中，而且优化器将能够在描述、标题和标签上使用索引(你需要添加您自己)，您应该离开。

尝试一下，看看您的查询优化器是否喜欢它...