Questions tagged [full-text-search]

Searching the text of a collection of documents or free text fields in a database to find those containing a word or combination of words.

122 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6
votes
0answers
3k views

PostgreSQL trigram GIST vs. GIN indexes

I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes. Initially, I started to use GIN ...
5
votes
1answer
356 views

Should Full Text Filter Daemon Service be a cluster resource?

Our newly installed and configures Sql Server Always On Availability Failover Cluster with Sql 2016 Enterprise Edition with four instances and six nodes. It includes the DB Engine Service and the ...
5
votes
0answers
4k views

Postgres 9.6.1 Full Text Search dictionaries for most spoken languages

Using Mac OSX Yosemite 10.10.5, I am trying to run full text search operations, such as to_tsvector, to_tsquery, etc and have a need for dictionaries in about 80+ languages. Postgres seems to only ...
4
votes
0answers
1k views

SQL Server 2016 Database Full Text Catalog not being updated

I have a database with a Full Text Catalog enabled, however the Catalog hasn't updated since about 4 weeks ago. There are no errors in the event logs, the SQL Server logs or the last SQLFT log file, ...
4
votes
0answers
471 views

PostgreSQL full text search: How to change what classifies as a “word separator”?

Using ts_debug I can see what symbols Postgres treats as (what I call) a "word separator". Example: SELECT ts_debug('english', 'Hello. ABC') Result: (asciiword,"Word, all ASCII",Hello,{...
3
votes
2answers
3k views

Full text search using words stored in another table

Is there a way to do a full-text search using words stored in another table? This is my scenario: Table_A contains phrases entered by users in field "UserInput" (and other stuff). I have a full-...
3
votes
0answers
3k views

SQL Server 2014 - Full text index never stops populating

I have a server that is running SQL Server 2014. I have full text indexes on some of my tables in my databases. The problem is that it's always saying populating and it never finishes. So my queries ...
3
votes
0answers
335 views

How to full text search localized enums?

Suppose an SQL database like PostgreSQL with a simple table of products: id | name | type ----+-------------+------- 10 | MacBook | 1 11 | iPhone | 2 12 | Boeing 747 | 3 13 | ...
3
votes
0answers
287 views

Making ordinal synonyms for Postgres full text search

I have a Postgres 9.3 database where users are mostly searching for locations by a combination of name and/or address (or fragments of). An example of an address would be "123 8th st". I've been ...
3
votes
0answers
261 views

custom population of full text index

Is there any way to populate a full text search index without storing the backing information in the database? I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
3
votes
0answers
309 views

SQL Server fulltext_semantic_languages

When trying to install a semantic search example, I'm running into a problem. There are some steps in the example to check if the 'new' Semantic Search and Full Text Search are installed. ...
2
votes
0answers
192 views

Creating a custom thesaurus dictionary on RDS

We're deployed on RDS using Postgres 11.4, and I'm hoping that there is some way to define a custom thesaurus dictionary. I think the answer is a hard no, based on this StackOverflow thread and this ...
2
votes
1answer
855 views

The fulltext filter daemon host (FDHost) process stops abnormally on SQL Alwayson Cluster

We added a new SQL Always-On Cluster (FCI) 2017 and some of the databases needed full text filter service. So we also added that feature to the install. Ever since we did that we have been getting ...
2
votes
1answer
123 views

Why is my Mac install of Postgres not tokenising Thai words?

On my Mac install of PG: =# select to_tsvector('english', 'abcd สวัสดี'); to_tsvector ------------- 'abcd':1 (1 row) =# select * from ts_debug('hello สวัสดี'); alias | description | token ...
2
votes
0answers
210 views

Is it better to store one tsvector column for all fields or one tsvector column per field?

Consider a simple movies table like this: CREATE TABLE movies( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title character varying(255), categories character varying(255), ...
2
votes
0answers
457 views

ts_headline() not highlighting properly for <-> operator

ts_headline() does not seem to highlight the input text in a way that respects the <-> operator. The following query: SELECT ts_headline('English','This Commercial Bank does not have any ...
2
votes
0answers
330 views

How to iterate over an array of strings and returns matching word

I'm testing text search on PostgreSQL and I would like to search for multiple terms based on a custom dictionnary I have : CREATE TABLE events (name text NOT NULL, description text NOT NULL); ...
2
votes
0answers
114 views

Full Text Index Data file much smaller after recreation

In order to speed up a field's datatype change, I: completely dropped the Full Text Catalog and a few NC indexes. changed the datatype (from VARCHAR to NVARCHAR). recreated the NC Indexes. recreated ...
2
votes
0answers
532 views

AWS RDS POSTGRES - Japanese/Chinese Full Text Search support

I have a requirement to support FTS (Full Text Search) of Japanese and Chinese on PostgreSQL. I am aware that there are extensions to handle this however AWS RDS PostgreSQL appears to restrict the ...
2
votes
0answers
568 views

FTS non-latin: text-search query contains only stop words or doesn't contain lexemes, ignored

I'm trying to use Full Text Search with Chinese characters using a postgres (9.6.1) FTS extension ZHParser. Following some of the examples here, I wanted to select my title column and match the title ...
2
votes
0answers
319 views

Create PostgreSQL fulltext Parser

I want to make a FTS on my text column on Postgres, the problem is that I want to search words with "_" between letters, like "the_event" but the default parser split those words and identify "_" like ...
2
votes
0answers
215 views

Why does score for mysql boolean match give different score?

I am attempting to use MySQL's fulltext search using Boolean match on multiple columns, with multiple search phrases. I cannot understand the score returned though. The score returned is typically an ...
2
votes
0answers
155 views

Restoring SQL Server 2012 Enterprise to Web Edition not possible due to full text partitioning

I have an SQL Server 2012 Enterprise database I’m trying to restore to SQL Server 2012 Web Edition instance. I’ve used sys.dm_db_persisted_sku_features to find features to remove before a backup/...
2
votes
0answers
291 views

Postgresql: Limits of tsquery field

I'm using a tsquery field in order to pre-compute batch searches, but I'm encountering this error while updating a tsquery field on a table: ERROR: row is too big: size 10704, maximum size 8160 The ...
2
votes
0answers
57 views

FullText search using only some of columns in index

I have a full text index on (name,category,description) as shown below. mysql> show create table phppos_items; +--------------+----------------------------------------------------------------------...
2
votes
0answers
1k views

operator class “gin_trgm_ops” does not accept data type tsvector

ALTER TABLE users ADD COLUMN full_text_vector TSVECTOR; CREATE INDEX full_text_index ON users USING gin(full_text_vector gin_trgm_ops); The last line is giving me the following error: SQLSTATE[...
2
votes
0answers
302 views

Improving performance of a weighted search query in SQLite3

For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight: CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, ...
2
votes
1answer
77 views

Index that mimics full-text-index

I have a database that is retrieving information quite slowly, I asked a question and was told to use full-text-indexing, this worked, but now we switched over to a .sdf file and Sql Server Compact ...
2
votes
0answers
1k views

Full-Text Index menu option not appearing in SQL Server Management Studio AND error when right clicking a table

I am using SQL Server Management Studio Version 11.0.3128.0 and SQL Server 2014 12.0.2361 Web Edition. I have successfully created a Full Text Catalog following these instructions: Cannot use a ...
2
votes
0answers
61 views

How do I get information about how an index set was created in Oracle?

A long time ago I created a CTXCAT index with an INDEX SET using ctx_ddl.create_index_set and ctx_ddl.add_index but forgot to save the DDL anywhere and now I need to recreate this index on another ...
2
votes
0answers
168 views

postgres - generating documents for full text search taking forever and eating up disk space

We use PostgreSQL and Django-Haystack with Elasticsearch for search on our service, and were thinking of switching to Postgres's built in full text search because of various annoyances using a NoSQL ...
2
votes
0answers
321 views

CONTAINS does not work with ROW_NUMBER

I have the following query: SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY ID DESC) , ID , Name FROM Clients WHERE CONTAINS (Name, 'Jose') My client table has a single ...
2
votes
0answers
166 views

SQL Server Full-Text Index with Stoplist

I've searched this site and the internet in general and can't find any information which answers my question, so I thought I'd post it here. I have Full Text search operating on a database in SQL ...
2
votes
0answers
589 views

Combining full-text-search queries

I have two tables in a SQL Server database with basic fulltext indexes: CREATE FULLTEXT CATALOG table1_catalog; CREATE FULLTEXT INDEX ON table1 ( name1 ) KEY INDEX pk_table1 ON ...
2
votes
0answers
46 views

improving this goal query

Goal joining the two tables: tbl_quickSearchParam (fulltext search results) against tbl_product ( active products : prod_status = 1, group by prod_image_name) create table tbl_product ( `...
1
vote
1answer
55 views

Full Text Search :SQL Server 2012

I want to enable full text search in sql server the feature already checked in the installation but in sql when i make this query select serverproperty('IsFullTextInstalled') The value 0 should be ...
1
vote
1answer
21 views

postgres parsing whitespace before vectorizing

Using PostgreSQL 11 to build a tsvector and create index for better search, and while trying to parse text, there is punctuation and/or whitespace characters that I need to handle differently than ...
1
vote
1answer
228 views

Mysql full text search with other indexed data

After getting some mysql optimization advice in this question. Was able to get the performance to the acceptable levels (when performing lookups using EventType and event_log_domain_id combo). To ...
1
vote
1answer
42 views

Converting and removing full text files

I have a database that was created many SQL Server versions ago that has Full Text files. It is currently on a SQL 2012 server. How can I confirm that there is no data in use in the Full Text index ...
1
vote
0answers
77 views

How to use ts_headline with multiple languages

I am using full-text search against a single table column. The data in the column could be of any language with no way to determine the language ahead of time. Assuming I only care about some subset ...
1
vote
0answers
36 views

Not able to update innodb_ft_num_word_optimize

We are using a AWS-RDS(MySQL 5.7) instance with 16GB RAM. We have a table with millions of entries and a FULL-TEXT index which needs to be OPTIMIZED. Apparently, FULL-TEXT optimization is very ...
1
vote
0answers
131 views

Why does an UPDATE to a full-text-indexed column lock the full text index?

I'm troubleshooting a deadlock on a SQL Server full-text index. From what I can see in SentryOne's monitoring, two queries deadlocked when trying to acquire locks on the full-text index; one query was ...
1
vote
0answers
188 views

MATCH() AGAINST() in boolean mode: order of patterns

Does the order of the patterns under against affect something? Here is a table I have: create table df_test ( t1 text, t2 text, FULLTEXT df_test_idx (t1 ASC, t2 ASC) ); Here are the rows I ...
1
vote
0answers
26 views

PostgreSQL FTS for quoted xml tags - is it possible?

I use Pg FTS to text-search database containing some technical descriptions/comments. Which in particular happen to contain XML snippets. Those are amazingly difficult to find by XML tags. I found ...
1
vote
0answers
35 views

MySQL FullText Search returning Zero rows

I have a query that works really good on searches in my table. However, I came across this certain search that resulted in no rows, even though I just added these rows and I know they are there (about ...
1
vote
0answers
1k views

Creating index on a big text column in postgres

We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE. The column is of type text and it is used ...
1
vote
0answers
80 views

More than one MATCH/AGAINST clause becomes too slow in MySQL

I have the following query: SELECT SQL_CALC_FOUND_ROWS gtd_items.* FROM gtd_items LEFT JOIN gtd ON gtd_items.gtd = gtd.id JOIN names importers ON gtd.importer = importers.id JOIN ...
1
vote
1answer
355 views

Full text search on larger text rows

I am wondering what is the best practice to use the right query in my case. The problem is I tried the LIKE and ILIKE and it was faster then pg_trgm and the postgres full text search, because they ...
1
vote
0answers
64 views

MySQL Fulltext indices slow after power outage

I have some large MySQL databases that have full text indices, and after a recent power outage, they are noticeably slower, and even failing on the larger ones. Previously, these queries were a couple ...
1
vote
0answers
27 views

sql server Full text different than like results

I am working on query which seraches for 4.01 using CONTAINSTABLE as " 4.01 ". I am expecting it would return all results similar to LIKE %4.01%. But the results are less in contains query. Here are ...