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.
463
questions
1
vote
0answers
26 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
326 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
53 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
20 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
17 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
32 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
44 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
38 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
61 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 ...
1
vote
1answer
50 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 ...
3
votes
2answers
175 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
72 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
66 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
124 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
28 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
16 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
33 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
136 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
98 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
30 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
104 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 ...
3
votes
1answer
119 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
15 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 ...
0
votes
1answer
45 views
MySQL: how to implement a text search without delimiter
I have a table table1 that contains several fields, some textual and other non-textual, on which I do search queries. Let's focus on some text fields, which I simply call field1, field2, field3.
The ...
1
vote
0answers
24 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 ...
0
votes
1answer
305 views
Using MATCH, AGAINST multiple times in MySQL fulltext search
I'm using MySQL database for my e-commerce project.
And here is my query for search:
SELECT DISTINCT p.id, p.name, p.price, p.created_at,
MATCH(p.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN ...
1
vote
0answers
184 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 ...
0
votes
1answer
213 views
mysql match against with same score on different input
I am trying to find the nearest match against a given name with mysql. The scoring is for the first two results the same, but looking at it I would rather get the second one as it fit's the name ...
3
votes
1answer
178 views
Find records with same string with extra character
OK, so I have a Microsoft SQL Server 2014 database table called owner with around 90,000 records with owner information, another called vehicle with vehicle information
Owner_Name ...
2
votes
1answer
36 views
Secondary filegroup for Full Text Index only - rebuild when lost
Here's our config:
We have all our data in PRIMARY file group
We have our Full Text Index in another SECONDARY file group which resides on a very fast (but very unreliable) SSD drive. By "unreliable" ...
1
vote
2answers
52 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
1answer
71 views
Proper use of FULLTEXT index for a column storing unique hash value
We have an InnoDB table called user, where we have a column called token (varchar(128)) where a unique md5-like value that represents whether a user is authenticated or not, is stored. This column can ...
1
vote
0answers
20 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 ...
0
votes
1answer
40 views
MySQL - fine-tuning full-text search
How can I fine tune MySQL full-text search for my information domain?
For example, I might have a keyword like js that I would like the search to recognize, but I don't think allow a minimum word ...
0
votes
1answer
32 views
MySQL - Boolean Full-text Search
I'm using MySQL's boolean full-text search for my search engine: https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html.
Assume I have a table with the following columns:
PRODUCTS
--------
id ...
1
vote
1answer
220 views
Why full text search on table with GIN index is still very slow
With what I've been able to gather thus far, if you need to run a full text search on a table in a PostgreSQL (psql 9.6.2, server 9.6.5) database with lots of entries (say on the order 1.2M+), the ...
2
votes
0answers
73 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),
...
1
vote
1answer
125 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 ...
2
votes
1answer
80 views
SQL Server FullText search on non-persisted columns
Let's say I have a table with 3 columns: Id, ColA and ColB. Then I created a FullText index:
CREATE FULLTEXT INDEX ON [Table]
(
ColA LANGUAGE 1033,
ColB LANGUAGE 1033
) KEY INDEX [...
0
votes
3answers
112 views
MySQL InnoDB Full Text Search is very slow when combined with other filters
I have a database with 23 million records which occupies about 16GB on HDD. 64-bit Operation System with 4Gb RAM.
For example the query
SELECT * FROM robjects
WHERE MATCH(type) AGAINST ('commercial'...
1
vote
2answers
77 views
Will moving the MATCH…AGAINST part in different part of the query (e.g. before evaluation of JOIN) will improve the performance?
Basically, I have the following query:
select `t`.*
from `tracking` `t`
where exists
(
select `ti`.`id` from `tracking_items` `ti`
inner join `cars` `c` on (`c`.`car_id` = `ti`.`...
0
votes
1answer
108 views
Make to_tsquery return OR of lexemes instead of AND
I'm using an dict_xsyn extension in postgres which returns multiple lexemes.
e.g.
CREATE EXTENSION dict_xsyn;
CREATE TEXT SEARCH DICTIONARY names_xsyn (TEMPLATE = 'xsyn_template');
CREATE SEARCH ...
0
votes
1answer
99 views
Full text search in PostgreSQL with search term including unrelated hyphen and ending
I've a problem in my DB (PGSQL 9.6) that I've not been able to resolve and I don't really even understand what options I might have (if any). Simplified I have a DB of multi-language news articles ...
1
vote
0answers
23 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
24 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
454 views
How to measure the size of InnoDB's fulltext index
How can I determine how much space MySQLs fulltext index is taking up, using innodb?
Solution would ideally be by querying an information_schema table rather than looking at the filesystem.
1
vote
0answers
25 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
116 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
1answer
208 views
Infinite scrolling with full text search in PostgreSQL
I am building a service that allows full-text searches and I would like to paginate the results.
I don't want to use OFFSET, so I opted for infinite scrolling with the keyset pagination, as outlined ...
2
votes
1answer
95 views
FTS is slow when searched word appears frequently in rows
I have a message table with a content column indexed for full text search:
create index message_fts on message using GIN(to_tsvector('english', content));
The problem I have and that I'd like to ...
