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.

Filter by
Sorted by
Tagged with
0
votes
1answer
238 views

The performance of mysql fulltext depend on order of keyword

I am having problem with mysql fulltext search. When I change the order of keyword, the search speed changes. For example I have 4 queries, all of them return same result: FAST (1s) SELECT * FROM ...
0
votes
1answer
109 views

PostgreSQL full-text search retrival from id gap

I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part). There is a postgres function where I SELECT concat_ws(' ', a."...
2
votes
2answers
95 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 ...
4
votes
2answers
6k views

Find rows with similar string values

I have a Microsoft SQL Server 2012 database table with around 7 million crowd-sourced records, primarily containing a string name value with some related details. For nearly every record it seems ...
0
votes
1answer
222 views

MYSql Full Text Boolean search and relevancy score improvements

I am using a mysql BOOLEAN full text search as shown in the query below and am getting what I think to be bad results that could be more relevant. Query: SELECT phppos_items.name,MATCH (phppos_items....
0
votes
1answer
90 views

MySQL indexing issue when trying to search for a part of a string/word

What we're running into: Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We ...
0
votes
1answer
166 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 ...
2
votes
1answer
35 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
28 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 ...
0
votes
1answer
122 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:...
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
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). ...
0
votes
1answer
52 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 ...
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' ) ...
1
vote
1answer
41 views

Exact behaviour of CHANGE_TRACKING AUTO

I am trying to understand population options for SQl server's full text indexes. My target is to access whether our currently used option (default = CHANGE_TRACKING AUTO) is the best to use in our ...
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 ...
0
votes
2answers
48 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
1answer
202 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
414 views

SQL Server crash with Reason 0x00000006 after a while on linux

Environment OS Ubuntu 16.04 x86_64 (also happening on Windows) DB SQL Server 2017 with FullText Search module (mssql-server and mssql-server-fts packages) Problem The issue that I'm facing is ...
3
votes
1answer
1k views

Wildcards in SQL Server full text search

While figuring out with full-text search I've found an article: FTS differentiates between a symbol defined as a wildcard and the other symbols. Here are a few symbols used in FTS as wildcards:...
2
votes
2answers
155 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 ...
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 ...
1
vote
0answers
41 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 ...
6
votes
1answer
110 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=@...
1
vote
1answer
42 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
1k views

How to find cause of SQL-Server Full-Text Index Auto-Population Error?

I have recently added a few full text indices to a database and AUTO populating is not working as I would have expected. Sample Table Definition CREATE TABLE [wrk].[WorkOrder] ( [id] ...
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-...
1
vote
1answer
38 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
1answer
691 views

How to get lexemes array from Postgresql ts_vector, sorted by lexems positional information?

Postgresql full text search to_tsvector() function returns a sorted array of lexemes. I need exactly the same function, but returning unsorted array of lexemes. Is there any such postgresql function ...
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 ...
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
2answers
928 views

Slow mysql query full text search tag database

In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. ...
0
votes
1answer
428 views

Full text indexes on big tables

I have a news website where I store my articles in a table that looks like that: Id title data Where data contains the content of the article and is the biggest column in the database by far. ...
3
votes
3answers
5k views

Search across all columns in a table

As the title says, I want to create a query that searches a keyword across all columns in that table. So I want to simplify this query: SELECT * FROM tblCustomers WHERE (col1 LIKE '%keyword%' OR ...
5
votes
1answer
733 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
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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
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)...
0
votes
1answer
39 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
0answers
50 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
vote
0answers
23 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 ...