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
0
votes
1answer
13 views

keyset pagination on extended query with full text search in postgresql

I have a database with 4 tables. These 4 tables hold users, their relations with each other (users following other users), the posts they make and the likes they receive. I have simplified the tables ...
1
vote
1answer
43 views

Multi language full text search using postgresql

I am trying to implement full text search using postgresql for some images. I am storing some information about my images into a json field in my table. This json has a tags key where I have multiple ...
0
votes
1answer
35 views

What's the best way to go about searching for short names?

I have a table that has CHAR in Korean for Soccer player names. All of these are under 12 characters, and while LIKE "%name%" works, Slight mistypes or variations don't work, and I've heard that LIKE ...
7
votes
1answer
149 views

Disable full-text logging (SQL Server)

Any way to disable FT logs completely? I spent hours googling - no luck. I get tons of "informational" messages literally every second. 2020-01-01 10:43:16.48 spid33s Informational: Full-text ...
0
votes
2answers
41 views

How to speed up full text query on a table with 4 million rows? (MariaDB)

I have an InnoDB full text table that serves the ajax-powered searchbox at the top of my website. I generate it with a daily script that pulls data from a dozen entity tables on the site, and ...
0
votes
1answer
19 views

SQL Server Full-text search for keywords without prefix matching

I have the following text in my NVARCHAR column "Ich bin eine SuperbProdukt" I have a full-text search enabled on the column and I can search for it by executing the following query that I wrote :...
2
votes
1answer
54 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
25 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
32 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
32 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 ...
1
vote
1answer
40 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 ...
1
vote
0answers
70 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
79 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
90 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
79 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
25 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
178 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
190 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
467 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
101 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
86 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
131 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
414 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
62 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
46 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
30 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
29 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
140 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
57 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
26 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
97 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
505 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
104 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
121 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
21 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
100 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
50 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
147 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
246 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
238 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
1k 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
189 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
133 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
325 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
30 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
20 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
58 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(...

1 2 3 4 5 10