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.
0
votes
0answers
24 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
13 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
10 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
26 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
86 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
17 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
17 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
35 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
11 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
42 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
23 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
71 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
79 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
118 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
169 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
25 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
42 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
30 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
8 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
25 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
27 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
120 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
51 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
69 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
60 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
62 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
56 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`.`...
-1
votes
1answer
75 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
75 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 ...
-1
votes
0answers
33 views
Finding nicknames and diminutives using PG text search
I'm trying to create a text search dictionary to match English given names. e.g. "Bob" and "Robert"
It looks like I need to use the dict_xsyn extension because it will match both ways?
I've created ...
0
votes
1answer
67 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
17 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
17 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
317 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
22 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
22 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
108 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
148 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
80 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
42 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 ...
1
vote
0answers
91 views
Why doesn't Posgres parse Arabic tokens correctly (using ts_debug)?
PostgreSQL has a few features related to full-text search. It knows how to parse a string into tokens (see parsers). For instance, here is how it parses Hello, friends and contributors. into tokens:
#...
1
vote
0answers
17 views
Adding Full Text to SQL Server 2014 - do I have to be careful about versions of installation media?
Hi I'm developer not a DBA, so I'm fairly clueless. I need to add Full Text Indexing to a SQL Server 2014 server. It's a production server on a HyperV VM.
Do I need to be careful about specific ...
2
votes
1answer
180 views
Keep both accented and unaccented versions in a tsvector column? [duplicate]
I am facing a situation where users SHOULD submit items that are written in plain English. However, it happens that names have accents - coming from various languages. I have a tsvector column in my ...
0
votes
1answer
66 views
Postgres to_tsvector is case sensitive on mac os
On PostgreSQL 10.4 on Ubuntu 10.4-2.pgdg16.04+1
select to_tsvector('simple', 'БОЛЬШИЕ БУКВЫ');
returns 'большие':1 'буквы':2
But on postgres 10.3 (installed by brew) on Mac Os High Sierra version ...
0
votes
1answer
78 views
MySQL architecture for different word clouds
I want to create a word cloud on my website based on the keywords people search.
I have one free text input field where something can be entered.
I have 3 different user types on my website (...
3
votes
1answer
246 views
SQL Server 2016 Restore Failure on DB Using Clustered Shared Volumes (Error 7627)
We are new to using clustered shared volumes and SQL Server 2016 Enterprise Edition.
When restoring SQL Server 2005 databases with full text search catalogs onto our new 2016 instance, we get the ...
1
vote
0answers
172 views
How to use full text search in Persian using Postgresql?
I'm using Postgresql 9.6 and I'd like to run a query like this:
select post_id
from comments
where to_tsvector('pg_catalog.persian', comments.body) @@ to_tsquery('pg_catalog.persian', 'کتاب') ;
...
2
votes
0answers
693 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:...
0
votes
1answer
404 views
Mysql Fulltext search doesn't return any results
I have an issue with a full text search in a MySQL table.
MATCH AGAINST returns no results even I if have 7 records containing the words I'm looking for. What can I do to make it return the rows?
...
1
vote
1answer
39 views
Transact-SQL script equivalent to the option: Apply Tracked Changes
Could anybody tell me what is the transact equivalent script of using the: Apply Tracked Changes menu?
