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.
497
questions
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(...
