We’re rewarding the question askers & reputations are being recalculated! Read more.

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.

115 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6
votes
0answers
94 views

Can oscillating number of fragment decrease FTS query performance?

I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly. Reading ...
5
votes
0answers
3k 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 ...
5
votes
1answer
730 views

Optimized MySQL “optional” FULLTEXT search on multiple tables

I need to select data with optional columns in where clause based on client input. my query is like this: SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt FROM a LEFT JOIN b ON a.b_id=b.id ...
4
votes
0answers
886 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
2answers
604 views

Performance problem using mysql fulltext search

I have a fulltext index on a column that contains an absolute file path (file_path), for a file indexing application. There are 500 000 rows in the table. The MySQL version is 5.7.19. Most, if not ...
4
votes
0answers
315 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 ...
4
votes
0answers
388 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
2k 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 ...
3
votes
0answers
299 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
265 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
303 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
119 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
358 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
279 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
94 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
427 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
256 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
191 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
281 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
284 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
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
57 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
165 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
311 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
161 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
558 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
45 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
23 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
26 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
1answer
71 views

Get stopwords of a InnoDB fulltext index

I can set stopwords for a MySQL fulltext index with SET innodb_ft_user_stopword_table ... before creating the fulltext index. But how can I figure out the existing stopwords of before created ...
1
vote
0answers
22 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
80 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
114 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
17 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
29 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
362 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
2answers
66 views

Search for a record name that begins with a slash in Postgresql Full Text Search

I'm trying to match a DB record (operation) that contains a slash with no results. The operation name field that I'm trying to search contains: some /word This is the search: SELECT "operations"...
1
vote
0answers
32 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
220 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
35 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 ...
1
vote
1answer
132 views

How to search for compound words, and get the word parts returned

I need to be able to search for compound words like "menneskerettighedsforkæmperens" (the human rights activist's) and find the words "menneske" (human), "rettighed" (right) and "forkæmper" (activist)....
1
vote
0answers
208 views

Why doesn't Posgres parse Arabic tokens correctly (using ts_debug)?

PostgreSQL has a few features related to full-text search. It knows how to parse a string into tokens (see parsers). For instance, here is how it parses Hello, friends and contributors. into tokens: #...
1
vote
0answers
20 views

Adding Full Text to SQL Server 2014 - do I have to be careful about versions of installation media?

Hi I'm developer not a DBA, so I'm fairly clueless. I need to add Full Text Indexing to a SQL Server 2014 server. It's a production server on a HyperV VM. Do I need to be careful about specific ...
1
vote
0answers
37 views

How to convert full-text search highlighted fragments into rows in PostgreSQL full-text search?

I am using PostgreSQL 10. I have a "booktbl" table which has 4 book_name paragraph_title content ts_content ts_content column is a tsvector column. When Iexecute this query: select book_name ,...