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.

Filter by
Sorted by
Tagged with
2
votes
1answer
33 views

PostgreSQL text search email parser does not handle + correctly

Default email parser does not parse emails with plus symbol (tested on 11.5): For test@example.com it works fine: select ts_debug('test@example.com'); +-----------------------------------------------...
0
votes
1answer
23 views

How to rank the product higher and the accessories lower? (MySql fulltext search)

I hope this is not a silly question. I have a table containg the products of an online store. I am using fulltext search in boolean mode. People search for, let us say, Apple iPhone 11. I don't ...
0
votes
1answer
29 views

Full text search over multiple CPU processes?

Is it possible to configure Postgres to split out a full text search over multiple CPU processes in an attempt to complete quicker? I'm running a full text search on 2 million records on a GIN ...
0
votes
0answers
31 views

FTS is slower on tsvector than text

I tested the performance of searches on two columns : text, tsvector. Somehow it appears that tsvector is slower than text. Also, there's some incosistency with planning time and execution time. It ...
0
votes
1answer
26 views

When might a tsvector field pay for itself?

I've been experimenting with tsvector indexes for Full Text Search, and see that it's a common practice to generate an store a vector in a column of type tsvector. We're on Postgres 11.4, but I've ...
0
votes
0answers
19 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 ...
1
vote
1answer
27 views

Postgres full text search on words, not lexemes

I've got a table with columns of text that I'd like to search by word, not lexeme. More to the point, that I'd like to have indexed by word rather than lexeme. We've got error dumps with a lot of code ...
0
votes
1answer
50 views

Postgres text column too large for an index - searching for IP address, so I don't think text search will work

I have a Postgres database with a column that has a large amount of text in it. There are potentially multiple IP addresses in the text, as well as a lot of other text. I need to do a query WHERE ...
0
votes
2answers
36 views

MariaDB FULLTEXT search with apostrophe, and mandatory words

An apostrophe / single quote (') should be part of a word in a FULLTEXT index. Admittedly I can't find this on the MariaDB website, but I'm assuming it should still behave like MySQL (version 5.5). ...
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
2answers
93 views

`CHANGE_TRACKING = AUTO` fails to populate full-text index

I have created full-text indexes on tables with the following options (simplified): CREATE FULLTEXT INDEX ON [schema].[table] ( ColumnA Language 'English', ColumnB Language 'English' ) ...
2
votes
2answers
91 views

SQL Server Full-text population deadlock

I've created a full-text index on a non-persisted computed column in a table (details here). However, every couple days, the population gets deadlocked and stopped. Specifically: DESCRIPTION: A ...
8
votes
1answer
441 views

Slow query when having 'contains' and '=' together in where clause

The following query takes about 10 seconds to finish on a table with 12k records select top (5) * from "Physician" where "id" = 1 or contains("lastName", '"a*"') But if I change the where clause to ...
0
votes
2answers
47 views

Which characters are considered “word” characters

Fulltext searchin with InnoDB in MySQL only includes certain characters referred to in the docs as "word characters" and mentioned here: For the built-in full-text parser, you can change the set of ...
1
vote
0answers
37 views

Query 4.5 million record postgresql database quickly [duplicate]

I have a SQL Server database with 4.5 million records. I reproduced the same database with the exact same 4.5 million records in a PostgreSQL database. When querying the SQL Server database, I am ...
1
vote
1answer
41 views

Postgres Full Text Search With Short Word Matching

I'm using Postgres' full text search to return results, but it seems any query with 3 characters or fewer returns no results. Is there a way to configure Postgres so that I can query with 2 and 3 ...
1
vote
1answer
198 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 ...
1
vote
1answer
37 views

language detection column content

I have a Postgresql table that has contents of a column either in English or Spanish Like so: 202707 Redness or swelling on the cheek. 202575 Preguntas acerca de medicamentos antivirales de la ...
1
vote
0answers
25 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 ...
0
votes
1answer
23 views

Benchmarking MongoDB full text search vs MSSQL full text search

I'm working on a project for comparing mongodb and mssql databases performance on full text search. I started with using YCSB benchmark but now that I have a deeper understanding about this benchmark, ...
0
votes
1answer
26 views

MySQL Full Text Search Excerpt

I am adding MySQL Full Text search to an intranet and one of the things i'd like to do is return an excerpt back in the search result that includes the search term(s) the user searched for. I ...
0
votes
0answers
16 views

Which characters are used as letters inside a fulltext index of MySQL?

I can add a fulltext index to MySQL tables. This will index all words of the related fields inside the table. But what are "words"? Indeed they incloses letters and digits (A-Z, a-z, 0-9) and maybe ...
1
vote
1answer
70 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
2answers
69 views

Get the matching condition in the SELECT list

Let's say my full text search query is below: USE AdventureWorks2012; GO SELECT Name FROM Production.Product WHERE CONTAINS(Name, '"chain*" OR "full*"'); GO I need one more column that ...
0
votes
1answer
36 views

How to set minimal token length for to_tsvector() function?

I parse xml documents with to_tsvector() function, and, sometimes, it produces tokens, which are shorter than 3 characters: '1':89,91 '2019':14 '25':4 I know, that to_tsvector([ config regconfig, ]...
0
votes
1answer
26 views

Optimize MySQL FULLTEXT searches to use another index

I'm running a price comparison service, with a database of products that aims to become quite large (in the millions or more). The Product table looks like this: CREATE TABLE Product ( id INT(10)...
1
vote
0answers
20 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 ...
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 ...
3
votes
3answers
417 views

Postgres trigram match acting strange for specific characters

I'm using pg_trgm to run a query, but I'm getting a lot of 1 matches when searching with a symbol difference. I have the following query: SELECT my_column, similarity('$ Hello', my_column) AS sml ...
4
votes
1answer
78 views

Is there a way stop the “.” (period) acting as a word breaker in FullText Indexes?

I know the period is a word breaker however I want to be able to search the fulltext catalog for a field containing the phrase "ASP.Net" or similar. Currently my search returns 'ASP' and 'Net' without ...
0
votes
0answers
82 views

Use Thesaurus as Synonym Dictionary for FTS?

As I understood, the function of this dictionaries are the same, except that Synonyms Dict do not support operation with phrases. Is It OK to use one dictionary for thesaurus and phrases in ...
0
votes
0answers
18 views

Why am I getting this counter-intuitive relevancy result to a MySQL full-text query?

I'm querying for +2019* +sponsor* +BGHDA* and getting this result: 2019 sponsor BGHDA #7 Springfield, WA 2019 03 04 relevancy: 34.139 2019 sponsor BGHDA relevancy: 26.087 Given that my ...
1
vote
0answers
79 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 ...
0
votes
0answers
47 views

Handling Dot (.) in SQL Full Text Search [duplicate]

I need to implement Full Text Search in SQL Server in our production databases. While working on it, I found that dot . in a column value cannot be considered as full text along with the remaining ...
1
vote
0answers
112 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
1answer
152 views

How does to_tsquery and to_tsvector decide which characters to omit?

I was quite surprised to find that TO_TSVECTOR('english', '平仮名ひらがな'); results in a vector of '平仮名ひらがな':1. For a system that omitted all non-alphanumeric characters and emojis I threw at it, I expected ...
2
votes
2answers
151 views

Force exact match from postgres ts_query

I've constructed a column of stem words called journal_tsv from the column journal_name. I've then created a GIN index on journal_tsv. Here's and example of the type of query I'm running: SELECT ...
5
votes
2answers
958 views

Why does CONTAINS not find matches at the end of the string?

I have a stored procedure in Microsoft SQL Server 2008 R2 (SP3) which searches a table for anything matching a @Keywords parameter that I pass in. It was too slow using LIKE as the table has 155,000 ...
0
votes
1answer
115 views

Filtering out duplicate domains from URL column using Postgres full-text search parsers

I have a PostgreSQL database containing pages and links downloaded by a web crawler, with the following tables: pages ---------- id: Integer (primary key) url: String (unique) title:...
6
votes
1answer
108 views

QueryStore plan forcing limitations

I have a DELETE statement running against a table with a full-text indexed column, a handful of foreign keys with cascade enabled. It looks like this: DELETE FROM dbo.STUDENTS WHERE STUDENTID=@...
0
votes
0answers
202 views

MySQL Error 1032 “Can't find record in table”

I'm getting MySQL 1032 "Can't find record in 'person'" errors for some queries in my database, and I cannot resolve them. Here's the table: CREATE TABLE `person` ( `id` int(11) NOT NULL ...
0
votes
0answers
29 views

Combine different index types in postgres

we have query for fulltext where we have also another condition. Can you tell me what is correct usage of multiple indexes of different type? for instance: select * from foo_tbl foo where foo....
0
votes
0answers
18 views

CONTAINS and FREETEXT search in the same query

How bad of an idea is it to include both a FREETEXTTABLE and a CONTAINSTABLE search in the same query? for example, something like: SELECT FT_TBL.Description FROM Production.ProductDescription AS ...
0
votes
0answers
49 views

Fulltext search in a CTE result - missing fulltext index

My question applies to MariaDB 10.2.7 The query WITH prefiltered AS ( SELECT id, fulltext_indexed_column FROM fulltext_table WHERE id < 30 ) SELECT * FROM prefiltered WHERE MATCH(...
0
votes
0answers
297 views

SQL Full-text Filter Daemon Launcher failing to start with error “Access is Denied”

When attempting to start the SQL Full-text Filter Daemon Launcher service through the SQL Server Configuration Manager, it attempts to launch, but then fails. In the event log we get the following ...
0
votes
1answer
101 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 ...
0
votes
1answer
50 views

Postgres text search has unexpected query conversion

I have a dictionary built in this way: DROP TEXT SEARCH DICTIONARY IF EXISTS simple_english_with_stopwords CASCADE; CREATE TEXT SEARCH DICTIONARY simple_english_with_stopwords (TEMPLATE = pg_catalog....
0
votes
1answer
162 views

Query Quill-Delta JSON array in Postgres

I need to store text in Quill-Delta format, which is in the form of a JSON array, e.g. ----------------------------------------------------------------- | id | title | content ...
4
votes
1answer
474 views

Full Text Search only performs 'starts with' searches

Pretty basic question, but how can I get the containstable search to do an actual 'contains' search, rather than a 'starts with' one? For example, from the examples in the documentation: CREATE ...
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 ...