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'月旦评')
用了新方法之后,查询果然快多了,即使数据量大的时候,几秒也能查完,而原先则是需要几十秒甚至几分钟,我还以为是我电脑的问题。
我猜想肯定很多用户都遇到这个问题了,但是我还没看到专门说这个的帖子(如果有,请告诉我),所以就写出来跟大家分享。
LIKE 这个用法应该彻底废弃,低效又低能,再加个通配符*,天下最一烂,没有之一。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
呜呜呜呜~~~~~
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
并不是,contains和like的结果不一样的。
contains是把文本拆成单词再进行对比的。
如果标题是'月旦评|文章标题'时,这样是可以的。
但如果是标日是'月旦评文章标题'里,使用CONTAINS函数时,这个文章就会被漏掉的。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
upvoted for arcange = 阿灿
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
哥!
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
没注意,还真自带一个“哥”
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
非常感谢 🙇
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
我很高兴能帮助你。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
阿灿哥的中文真的不错!
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
早知道那天和你说中文了哈哈哈哈哈
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
arcange会说中文?哇!
他之前也有在我的po文告诉我关于使用CONTAINS来代替LIKE
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
会一点。可能跟我的德语水平差不多。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
大鹏哥那么谦虚,我有个德国朋友,在那儿几乎都用德语,很难用英文交流.
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
是啊是啊说多了都是泪😭
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
回中国吧,哈哈哈
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
我学会了几个月的中文说话。不幸的是,我不做法不够。😢
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
好厉害!!!👏
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
@dapeng, 代码/编程/IT搞得很溜啊,陶醉本尊了,快扶我一下...
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Congratulations @dapeng! You have completed some achievement on Steemit and have been rewarded with new badge(s) :
Click on the badge to view your Board of Honor.
If you no longer want to receive notifications, reply to this comment with the word
STOP
To support your work, I also upvoted your post!
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
续费好了?
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
是的,但是……我想想再说。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
这就是我和 @arange那天研究的内容哈哈。
不过不能这样用的,因为contains这个函数是取单词的,用空格隔开能取到,如果用别的符号应该也能,但如果连在一起比如“月旦评的故事”就搜不到的。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
读了十遍,你这条回复我仍然没读懂。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
微信上和你说
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit