在此处发布一个关于如何提高SQL Search方法速度的问题后,建议我更新我的表以利用全文搜索。这就是我现在所做的,使用Gist索引来加快搜索速度。在一些简单的查询中,我注意到了一个显着的增加,我很高兴。
然而,我很难搜索部分单词。例如,我有几个包含Squire(454)的记录,而且我有几个包含Squirrel(173)的记录。现在,如果我搜索Squire,它只会返回454条记录,但我也希望它返回Squirrel记录。
我的查询看起来像这样
SELECT title FROM movies WHERE vectors @@ to_tsoquery('squire');我以为我可以做 to_tsquery('squire%')但这不起作用。 如何获得它以搜索部分匹配?
另外,在我的数据库中,我有记录是电影和其他只是电视节目。这些名称与名称不同,因此明斯特是电视节目,而明斯特是该节目的电影。我想要做的就是只搜索电视节目和电影。关于如何实现这一目标的任何想法?
问候 Anthoni
解决方案甚至使用 LIKE ,你将无法从 squire%获得'松鼠',因为'松鼠'有两个'r'。要获得Squire和Squirrel,你可以运行下面的查询: pre $ SELECT title FROM movies WHERE vectors @@ to_tsquery('squire | squirrel );为了区分电影和电视节目,您应该在数据库中添加一列。但是,这种猫的皮肤有很多种方法。你可以使用子查询来强制postgres首先找到匹配'squire'和'squirrel'的电影,然后搜索该子集来查找以'''开头的标题。可以创建索引以便在 LIKE'%...'搜索。
没有探索其他索引的可能性,你也可以运行这些 - 他们找到哪个是最快的:
SELECT title FROM( SELECT * FROM电影 WHERE vectors @@ to_tsquery('squire | squirrel'))t WHERE title ILIKE'%';Following a question posted here about how I can increase the speed on one of my SQL Search methods, I was advised to update my table to make use of Full Text Search. This is what I have now done, using Gist indexes to make searching faster. On some of the "plain" queries I have noticed a marked increase which I am very happy about.
However, I am having difficulty in searching for partial words. For example I have several records that contain the word Squire (454) and I have several records that contain Squirrel (173). Now if I search for Squire it only returns the 454 records but I also want it to return the Squirrel records as well.
My query looks like this
SELECT title FROM movies WHERE vectors @@ to_tsoquery('squire');I thought I could do to_tsquery('squire%') but that does not work. How do I get it to search for partial matches ?
Also, in my database I have records that are movies and others that are just TV Shows. These are differentiated by the "" over the name, so like "Munsters" is a TV Show, whereas The Munsters is the film of the show. What I want to be able to do is search for just the TV Show AND just the movies. Any idea on how I can achieve this ?
Regards Anthoni
解决方案Even using LIKE you will not be able to get 'squirrel' from squire% because 'squirrel' has two 'r's. To get Squire and Squirrel you could run the following query:
SELECT title FROM movies WHERE vectors @@ to_tsquery('squire|squirrel');To differentiate between movies and tv shows you should add a column to your database. However, there are many ways to skin this cat. You could use a sub-query to force postgres to first find the movies matching 'squire' and 'squirrel' and then search that subset to find titles that begin with a '"'. It is possible to create indexes for use in LIKE '"%...' searches.
Without exploring other indexing possibilities you could also run these - mess around with them to find which is fastest:
SELECT title FROM ( SELECT * FROM movies WHERE vectors @@ to_tsquery('squire|squirrel') ) t WHERE title ILIKE '"%';or
SELECT title FROM movies WHERE vectors @@ to_tsquery('squire|squirrel') AND title ILIKE '"%';
更多推荐
PostgreSQL:全文搜索
发布评论