1 min read

Improve the query with 'CONTAINS' and 'depth' 用这条指令来大幅度提高 SteemSQL 的查询性能

原文链接: steemit, cnsteem, busy, chainbb, steemdb, steemd, busy, markdown

Several days ago, I saw on my post a comment by @arcange, indicating that my query to SteemSQL affected the performance of the server. I contacted @arcange immediately. He kindly explained to me with patience that I should use CONTAINS() instead of LIKE, and depth=0 instead of title = '' to search efficiently. Now my search is much faster than before. Great thanks to @arcange! You can vote @arcange as a witness so as to support his great work!

This post tells the details of the story mentioned above. Hopefully it could help other SteemSQL users.

不久前,我的某篇帖子收到来自阿灿哥(arcange)的一条回复:

Hello @dapeng,

You are issuing query to SteemSQL that negatively impact the infrastructure performances and penalize others users.

Please contact me on steem.chat or on Discord!

我英语不好,大概感觉是好像我不知怎地把 SteemSQL 服务器给破坏了,好像是捅了大漏子。这个问题不久前 @tvb 跟我讨论过,没想到现在轮到我了,吓得我赶紧去 chat 联系阿灿哥负荆请罪。

原以为会被骂一通,谁料想阿灿哥非常 nice,给我如此这般详细解释了一番。

他说,我在搜索 SteemSQL 数据库的时候,用的两个指令效率太低。一个是 LIKE,一个是title <> ''。例如,我搜索“华语好声音”活动的帖子时,搜索的条件是以 ‘cn-voice’ 为标签 + 标题不为空:

SELECT 
    * 
FROM 
    Comments 
WHERE 
    title <> '' 
    AND
     json_metadata LIKE '%"cn-voice"%'

阿灿哥说,LIKE这个操作极度耗费资源。SteemSQL 最近支持对帖子的标题、正文和 json 元数据进行全文检索了,所以应该用 CONTAINS来代替 LIKE,并且用 depth = 0 来选取标题不为空的帖子(> 0的话就是选取评论):

SELECT
    * 
FROM
     Comments 
WHERE 
    depth = 0 
    AND 
    CONTAINS(json_metadata, "cn-voice")

阿灿哥会说中文,特意告诉我支持中文检索哦,例如:

SELECT
    *
FROM
    Comments 
WHERE
    CONTAINS(title,  N'月旦评')

用了新方法之后,查询果然快多了,即使数据量大的时候,几秒也能查完,而原先则是需要几十秒甚至几分钟,我还以为是我电脑的问题。

我猜想肯定很多用户都遇到这个问题了,但是我还没看到专门说这个的帖子(如果有,请告诉我),所以就写出来跟大家分享。

感谢阿灿哥!可以通过投票 @arcange 当见证人来支持他。