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
7 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
19 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
33 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
41 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
36 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
161 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
20 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
34 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
22 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
6 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
20 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
19 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
75 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
47 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
49 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
54 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
55 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
49 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
56 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
57 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
0answers
25 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
47 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
16 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
15 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
237 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
21 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
104 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
110 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
74 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
38 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
69 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
135 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
51 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
71 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
159 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
141 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
522 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
329 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
34 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?
1
vote
0answers
190 views
ts_headline() not highlighting properly for <-> operator
ts_headline() does not seem to highlight the input text in a way that respects the <-> operator.
The following query:
SELECT ts_headline('English','This Commercial Bank does not have any ...
6
votes
1answer
705 views
How to search hyphenated words in PostgreSQL full text search?
I have to search for hyphenated words like 'good-morning', 'good-evening', etc.
My query is:
select id, ts_headline(content,
to_tsquery('english','good-morning'),
...
0
votes
1answer
345 views
Optimize wildcard searching for a NVARCHAR(MAX) column
I have been asked to improve the searching capabilities of a LIKE '%SOMETEXT%' predicate on an NVARCHAR(MAX) column. I am aware of full text indexes but am not sure if they are the best strategy.
A ...
1
vote
0answers
30 views
How to convert full-text search highlighted fragments into rows in PostgreSQL full-text search?
I am using PostgreSQL 10. I have a "booktbl" table which has 4
book_name
paragraph_title
content
ts_content
ts_content column is a tsvector column. When Iexecute this query:
select
book_name ,...
0
votes
3answers
72 views
SQL Server FullText searching on text that is slightly obfuscated before insert
Our app slightly obfuscates text before insertion to the database. This is done by the application (C#) and not the database. (This is not encryption but just to stop people who are looking at the ...
1
vote
1answer
191 views
Postgres: Dictionary to remove leading zeroes from alpha-numerical words
I am running a full text search in Postgres 10 over texts which include a variety of alphanumerical IDs (as well as 'real' text).
My clients want to be able to find those IDs without the need to enter ...
-1
votes
1answer
31 views
What does FHQT or FQA mean?
I was reading this post by Andres Pascal, and I came accross a few new acronyms,
As I understand, you need to search strings by similarity (using levenshtein or any other metric distance). In that ...
1
vote
0answers
77 views
Searching 100000´s of Varchar(max) columns for 1000´s of keywords/sentences
I need some advice/ideas.
I have a table containing job announcements approx. 250,000 records
In another table is 6000 keywords/sentences from applicant cv's historically regarded by the government ...
0
votes
1answer
23 views
Full-text search does not work if query begins from 0
I have a table with users and their addresses. Some flats looks like "p06"
This query does not find anything:
SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +...
