假设我有一个表代表主要的国家司(例如国家):
create table principal_country_divisions (
id int primary key,
name text not null,
country_code char(2)
);
insert into principal_country_divisions values (1, 'New York', 'US');我希望用户能够通过“纽约”、“纽约”或“纽约州”这样的同义词轻松找到纽约。
所以我有一个同义词表:
create table synonyms (
syn text,
name text,
primary key (syn, name)
);
insert into synonyms values
('NY', 'New York'),
('New York State', 'New York');什么是有效和容易的方式来查询这一点,并返回一个记录的纽约?
特别是,他们应该能够找到默认名称'New York‘或任何同义词的结果:
select * from principal_country_divisions where name = 'NY';
result: {1, 'New York', 'US'}我想我会从这样的开始:
select
id,
name,
country_code
from principal_country_divisions a
where name = 'NY'
or exists (select 1 from synonyms where name = a.name and syn = 'NY')我可以只用一个视图来完成这个任务,还是应该使用一个函数?
发布于 2013-12-17 05:09:15
首先,在principal_country_divisions上有一个整数主键。使用它。由于多种原因(存储大小、索引大小、更快的整数算法、不涉及排序规则、固定长度),比通过name连接更有效。
create table principal_country_divisions (
country_id int primary key
,name text not null
,country_code char(2)
);
create table synonyms (
country_id int REFERENCES principal_country_divisions (country_id)
,syn text
,primary key (syn, country_id)
);syn需要索引的第一列(pk),你已经说对了。所附索引自动涵盖synonyms.syn上的等式测试。
确保在principal_country_divisions.name上添加索引:
CREATE INDEX foo ON principal_country_divisions (name);如果您想要匹配模式,而不是整个字符串,那么任务就会变得更加复杂。
接下来,你怎么能确定
把一张唱片还给纽约?
显然,name和syn是一样的。这两列都没有唯一的约束,甚至在syn上也没有唯一的约束。否则,EXISTS查询是一种很好的方法--通常是快速的。你只需要避免多行。EXISTS的额外好处是仅从synonyms中消除重复项,但pk排除了这一点。这对本案来说可能更快:
SELECT DISTINCT ON (1)
a.country_id, a.name, a.country_code
FROM principal_country_divisions a
LEFT JOIN synonyms s USING (country_id)
WHERE a.name = 'NY'
OR s.syn = 'NY'
-- ORDER BY 1, <more expressions to pick from peers>正如您所评论的,LEFT JOIN是为了保留name中的查找结果。
在多个查找的情况下,可以通过添加更多的ORDER BY表达式来选择要选择的内容。不过,领先专栏必须同意DISTINCT ON的观点。有关答案的详细资料。
https://dba.stackexchange.com/questions/55142
复制相似问题