问题[string-searching](dba)

我有一列包含物种列表的字符串：

+----------------------------------------+
|                species                 |
+----------------------------------------+
| Dinosauria, Ornitischia, indeterminado |
| Sirenia                                |
| Dinosauria, Therophoda                 |
| Dinosauria, Therophoda, Allosaurus     |
| and so on...                           |
+----------------------------------------+

我正在寻找一种在 PostgreSQL 12 中列出和计算所有唯一名称的方法，例如：

+---------------+-------+
|    species    | count |    
+---------------+-------+
| Dinossauria   | 3     |
| Ornitischia   | 1     |
| indeterminado | 1     |                        
| Sirenia       | 1     |    
| Theropoda     | 2     |              
| Allosaurus    | 1     |                 
+-----------------------+

MySQL 版本是 5.7.25

我有桌子

CREATE TABLE `applications` (
  `id` varchar(25) NOT NULL,
  `application` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

“应用程序”是大约 50kb 的 xml 文本。

该表包含大约 120k 条记录。

当我进行搜索时

Select * from `applications`
where 
application like '%<l:name>8085%</l:name>%'

查询因 30 分钟超时而失败。

如果我做

Select A.* from (
Select * from `applications`
Limit 0,1000
)A where 
A.application like '%<l:name>8085%</l:name>%'

查询在 6 秒内完成。

所以通过分页我可以在 120*6 = 720 秒内得到查询结果

有趣的是，限制为 0,10000 的相同查询在 120 秒内完成，限制为 0,100000 的查询因超时 30 分钟而失败

从这篇 SO 帖子中，我了解到可能 longblob 查询性能与行数不是线性的。

我终于完成了通过分页和连接结果来获取查询结果。

Select A.id from (
Select * from `applications`
Limit 0,10000
)A where 
A.application like '%<l:name>8085%</l:name>%'

Select A.id from (
Select * from `applications`
Limit 10000,20000
)A where 
A.application like '%<l:name>8085%</l:name>%'
......

问题是如何更方便地获得结果。

搜索字符串会有所不同，并且事先是未知的，将列类型更新为TEXT现在不是一种选择，因为这需要应用程序重构，而此时没有人想要。查询是手动更新的一部分，所以执行时间不是问题。

伦纳特：

题外话，但即使没有人更新表，您也可能会错过某些行。如果要保证检查所有行，则需要与 limit 一起进行 order by。

添加ORDER BY和检查更新是有意义的，谢谢。

您好我正在尝试在包含数组的表上使用 pg_trgm 实现某种模糊字符串匹配jsonb，这是一个示例数据：

      name
------------------
[{"firstname": "jhon", "lastname": "doe"},{"firstname": "jhonny", "lastname": "doe"}]
------------------    
[{"firstname": "jane", "lastname": "doe"},{"firstname": "janne", "lastname": "doe"}]

这是我当前查询的示例：

SELECT * FROM persons WHERE exists(
    SELECT * FROM json_array_elements(persons.name::json)
        WHERE word_similarity('jhon', (COALESCE(value->>'firstname', '') || ' ' || (COALESCE(value->>'lastname', ''))) ) >= 0.7);

它可以完成这项工作，但速度很慢（没有索引）。我的问题是我可以做些什么来加快搜索（索引，替代查询）？TIA

我有一个 Postgres 数据库，其中包含有关网站的信息和一个名为sitescolumn的表host。这已经有一个文本模式操作的索引，非常适合从域名的开头进行搜索：WHERE host LIKE 'www.bran%但对于部分匹配（例如WHERE host LIKE '%.bran%'导致扫描的部分匹配）来说不是很好。目前大约有 750 万台主机，因此搜索性能已经很明显了。

根据下面的建议，我添加了一个 trigrm 索引（您需要启用扩展才能执行此操作：

create extension pg_trgm;

然后添加一个合适的索引：

CREATE INDEX trgm_idx ON sites USING GIST (host gist_trgm_ops);

与所有索引一样，这可能需要一些时间，具体取决于数据库的大小。

在索引之前，我的查询大约需要 180 秒才能在 750 万个中找到 200 个项目，但这因相关子查询而变得复杂，这似乎使问题复杂化并主导查询，并建议我应该首先考虑优化其他内容，特别是日期索引。

所以比较的结果是：使用 text_ops 索引大约需要 2s，使用 trigram 大约需要 7。我想如果我创建一个只包含主机名相关部分的索引，我可能会更幸运。

我有这个 ：

CREATE TABLE users
(
    first_name varchar(60) NOT NULL, 
    last_name varchar(60) NOT NULL
);

INSERT INTO users
    (first_name, last_name)
VALUES
    ('Sylvia ', 'Hoeks'),
    ('Harrison', 'Ford');

我得到这样的清单

SELECT first_name || ' ' || last_name AS user_full_name FROM users;

但我希望能够在该列上进行搜索user_full_name。没有子查询或 CTE 是否可能（如下所示）？

WITH users_list AS (
    SELECT first_name || ' ' || last_name AS user_full_name
    FROM users
)

SELECT * FROM users_list WHERE user_full_name ILIKE '%lvi%';

我试过第一列！？

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE 1 ILIKE '%lvi%';
/*
ERROR:  operator does not exist: integer ~~* unknown
LINE 1: ...|| last_name AS user_full_name FROM users WHERE 1 ILIKE '%lv...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
*/

我希望能够做这样的事情。

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE user_full_name ILIKE '%lvi%';

目前我这样做：

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE first_name ILIKE '%lvi%' OR last_name ILIKE '%lvi%';

如果需要，小提琴：https ://www.db-fiddle.com/f/c3HANw8M9W7kSWeEwidpne/0

我想匹配WHERE子句中字符串的开头。虽然我认识到数据库性能通常取决于底层数据结构，但是否有这样做的最佳实践？有没有一种选择总是优于其他选择？

我想避免LIKE，因为我认为它的效率会低于LEFTor CHARINDEX。据我所知，我的选择如下。欢迎其他建议：

DECLARE @MyField as varchar(10)
SET @MyField = 'HELLOWORLD'
SELECT 1 WHERE @MyField LIKE 'HELLO%'
SELECT 2 WHERE LEFT(@MyField, 5) = 'HELLO'
SELECT 3 WHERE CHARINDEX('HELLO', @MyField) = 1