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.
-2
votes
1answer
31 views
Questions about Full-Text Search in SQL Server
If I load a 150kb text file into an nvarchar(max) column, then select the results and paste them into notepad, the resulting file is only 42kb.
Second question concerns 8kB pages. What influence have ...
1
vote
0answers
23 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 ...
-3
votes
0answers
34 views
Full-text search (FTS) in databases, which database is the best for it? [on hold]
I have problem, I consider database(MS SQL SERVER, ORACLE, POSTGRESQL) for full text search. I saw that MS has it, POSTGRESQL too, but ORACLE not. Can you advice me which database will be the best?(...
0
votes
0answers
31 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
25 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
42 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
27 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 ...
2
votes
2answers
86 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
63 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
55 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
86 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
14 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
20 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
82 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
96 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
23 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
60 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
67 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
14 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
44 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
218 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
128 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
181 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
174 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
32 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
48 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
46 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
14 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
34 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
28 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
167 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
61 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
103 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
75 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
91 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
69 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
104 views
How to create trigram by joining two columns
I am new to sql so this might be overly simple question that I am confused about. I am trying to create trigram index by joining three columns (images, tags, images_tags). I used string concatenation ...
0
votes
1answer
96 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
87 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
20 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
18 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
402 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.
0
votes
0answers
25 views
sql server: fts_parser , trouble understanding it
select display_term from sys.dm_fts_parser('"3.14"', 1033, 0, 0);
Result: 3.14 and nn3d14
so does that mean it searching for phrases having 3.14 anywhere and what does nn3d14 mean? what is it ...
1
vote
0answers
23 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
111 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)....
0
votes
1answer
181 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
86 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 ...
0
votes
0answers
43 views
Index full population with AlwaysOn
Getting an error when running:
ALTER FULLTEXT INDEX ON MyTbl START FULL POPULATION
error:
Msg 7654, Level 16, State 1, Line 3
Unable to obtain the population status of the table or indexed ...
