sqlite查询优化
查询
SELECT * FROM Table WHERE Path LIKE 'geo-Africa-Egypt-%'
可以优化为:
SELECT * FROM Table WHERE Path >= 'geo-Africa-Egypt-' AND Path < 'geo-Africa-Egypt-zzz'
但是如何做到这一点:
select * from foodDb where Food LIKE '%apples%";
如何优化?
The query
SELECT * FROM Table WHERE Path LIKE 'geo-Africa-Egypt-%'
can be optimized as:
SELECT * FROM Table WHERE Path >= 'geo-Africa-Egypt-' AND Path < 'geo-Africa-Egypt-zzz'
But how can be this done:
select * from foodDb where Food LIKE '%apples%";
how this can be optimized?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
一种选择是冗余数据。 如果您要大量查询某个列中间出现的某些固定字符串集,请添加另一列,其中包含是否可以在另一列中找到特定字符串的信息。
对于任意但仍可标记的字符串,另一种选择是创建一个字典表,其中包含标记(例如
apples
)以及对标记出现的实际表的外键引用。一般来说,sqlite 在设计上不太擅长全文搜索。
One option is redundant data. If you're querying a lot for some fixed set of strings occuring in the middle of some column, add another column that contains the information whether a particular string can be found in the other column.
Another option, for arbitrary but still tokenizable strings is to create a dictionary table where you have the tokens (e.g.
apples
) and foreign key references to the actual table where the token occurs.In general, sqlite is by design not very good at full text searches.
如果它更快的话我会感到惊讶,但你可以尝试 GLOB 而不是 LIKE 并进行比较;
It would surprise me if it was faster, but you could try GLOB instead of LIKE and compare;