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.
602
questions
-1
votes
1
answer
31
views
Full text search in poatgres using tsvector and tsquery [closed]
I have a query like below
Select * from abc where (to_tsvector('english', firstname) @@ to_tsquery('english', 'a:*'));
is not working
But same query aa is working
Select * from abc where (...
- 7
0
votes
2
answers
40
views
Full-text search performance is input-dependent and is extremely slow
I am trying to speedup a fulltext search query. The testing table has 2 million rows, their values are md5 strings. I have created several indices to see, what performs best, but the planner chooses (...
0
votes
1
answer
56
views
Full Text Index stuck
We recently upgraded our SQL Server database server.
We have a search on our website that uses a full text index to search.
I am now trying to figure out why the full text index appears to be stuck.
I ...
- 203
0
votes
1
answer
246
views
Text search on email domain with postgres
I'd like to have a text search that can also retrieve results matching the domain name in an email address:
Say I have this table of tweets
create table tweets (
id uuid primary key,
tweet text ...
- 101
0
votes
1
answer
92
views
Optimizing a MySQL street address table for search
I'm developing an app that has a MySQL table, addresses, which stores events that happen at a given US address. I receive this data from a third-party in large chunks at set intervals. The table has ...
- 111
0
votes
0
answers
15
views
filestream fulltext-index language-for-wordbreaker
I need to use "_" as a word breaker for full text search. Setting the language as Korean does what I need:
SELECT * FROM sys.dm_fts_parser ('Happy_Hour',1042,0,0)
When I put "'...
- 1
0
votes
0
answers
535
views
Best Way to search for a string containing a letter or a word in SQL Server (Full Contains Search)
I have a requirement where I need to search a few columns in the table to see if the letter or word passed in as parameter is contained in any of those columns and return the first 100 rows. For ...
- 25
1
vote
1
answer
967
views
Search a text column for a string from a list, and return the found string
I'm trying to search larger text for one of multiple values, lets say "Jack", "Jill", or "Bob" (the words can be 50 characters long).
With Snowflake I have something like
...
- 1,651
0
votes
1
answer
118
views
fuzzy search levenshtein vs full text indext
What would perform better on a big table ~30million records grouped into 100_000 indexed buckets each having between 1 to 10_000 records?
MySQL's full text index on the entire dataset (presumably ...
0
votes
0
answers
148
views
SQL search for a pattern in the middle of a string (infix search) without full table scan
With SQL, I need to select rows which have a pattern like %hello% (i.e. <anything>hello<anything>) in a column, with potentially 100 millions of rows or more.
In the answer Infix search in ...
- 161
1
vote
0
answers
37
views
Issue with stored procedure returning no results
I have the following MySQL procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `ESOLUTIONS`.`getPlatformByAttribute`(
IN attributeName VARCHAR(255),
IN startRow INT
)
BEGIN
SELECT
...
- 33
0
votes
0
answers
45
views
SQL Server Full Text Master Merge cadence
I've noticed a few things around SQL Server full text searches around the concept of a "master merge".
The documentation implies this really only happens after a population: Improve the ...
- 1,490
1
vote
1
answer
176
views
SQL server full text search with wildcard in search parameter
I have a table with a full text index on it and I am looking for rows that contain a string like "p.G213*" but SQL server is treating the star like a wildcard, when I am trying to search for ...
- 11
1
vote
0
answers
235
views
The fulltext filter daemon host (FDHost) process has stopped abnormally Event 30089
ENV: SQL 2017std CU25 8 CPU 48GB mem
Vendor Software Full Text Catalog setup.
Found in FT crawler log:
A string of these 0x80004005 errors:
2023-01-27 07:49:02.34 spid19s Error '0x80004005' ...
- 21
0
votes
0
answers
126
views
Source a synonym dictionary from a table in PostgreSQL
I am keeping a table with numerous aliases for a given canonical product name.
I would like to use that table as a synonym dictionary for full-text search. Unfortunately, PostgreSQL wants to load the ...
- 81
0
votes
1
answer
707
views
How to index a Postgres Full-Text Search across relations
I'm in the process of adding indices to support full-text searching in our PostgreSQL (12) database. I noticed that everything works well if all searched columns are in the same table, but that is not ...
- 103
1
vote
1
answer
176
views
Error when replicating Full Text indexes from SQL Server to an old Azure SQL database
When trying to deliver a snapshot for the transactional replication from SQL Server database to an old Azure SQL database (the database created long before Microsoft introduced Full Text support in ...
1
vote
0
answers
191
views
postgres ts_headline function is not returning matches it should during full text search
Background:
I've been using Postgres full-text search and it has met my needs quite well. Though there is some unexpected behavior that I cannot seem to wrap my head around. It has to do with the full-...
- 61
1
vote
0
answers
109
views
How to do a fulltext search over multiple tables (left joined) at once?
I've got a table named Customer, e.g.
CREATE TABLE Customer(
Id bigint IDENTITY(1,1) NOT NULL,
Name varchar(100) NOT NULL)
with other dependent tables, e.g. Address
CREATE TABLE Address(
...
- 111
0
votes
0
answers
183
views
MySQL InnoDB Full-text search: Override default stopword list
I'm using MySQL8.0 InnoDB and its full-text search features. The default stopwords list of InnoDB only contains 36 words. Extending the stopword list to 500+ words helps to lift our full-text queries ...
- 21
2
votes
1
answer
99
views
Full-text search with hyphenated integers
Compare the following search queries:
SELECT
to_tsvector('yellow-green') @@ to_tsquery('yellow & green') as word_word,
to_tsvector('Apollo-11') @@ to_tsquery('apollo & 11') as ...
- 81
0
votes
1
answer
33
views
Minimizing MySQL full text query times on news database
I have a MySQL Database of 250,000 news articles. With time, it's size will continue to increase by at least 250,000 articles per year.
I'm doing full text searches on this database to find articles ...
3
votes
0
answers
324
views
Full-text search index across columns and tables
I am building a document database and I would like to utilize the full-text search capabilities of PostgreSQL. The documents do not all follow a clean schema, so I decided to store some values in ...
- 81
0
votes
1
answer
177
views
How to search for partial matches from a GIN index in a multi-word PostgreSQL query?
I have a GIN index in a Postgres database that I'm currently using to do full text search. So if a tsvector field contains the vectorized form of "mary had 4 little lambs" then I could ...
- 1,324
1
vote
0
answers
113
views
Search query in large multi-tenant database
I have a fairly large table (700k rows) containing the main object of my application. This is a multi-tenant app. Typically, users have access to 1k rows of this table, but there are edge-cases with ...
- 123
0
votes
1
answer
1k
views
Access Search Form is not including null values
Sorry if this is an overly easy question, I don't use access often but was asked to make a search form for a table our company uses to track remake parts. I have a form created with a series of ...
0
votes
1
answer
743
views
MySQL FULLTEXT Match exponentially slower with search phrase
I'm trying to track down some performance issues in my FULLTEXT MATCH() AGAINST() query, and it seems to be related (at least in part) to queries requiring an exact phrase.
My table stores system ...
- 3
2
votes
0
answers
278
views
Postgresql full text search without diacritics (effective unaccent)
I've similliar question as Postgres full text search with unaccent and inflection (conjugation, etc.) .
I want to use FTS but in "unaccent" mode. I'll explain with the example. The "...
- 21
2
votes
0
answers
199
views
Indexing 250M URLs for infix search in PostgreSQL
I have a very large Postgres table with about 250M rows, and a text column url. I want to be able to search against this in a performant way, eg. SELECT url_id FROM urls WHERE url ILIKE '%somewebsite....
- 183
3
votes
1
answer
184
views
Full text search wont find post codes "NNx" where "x" is a digit
I have a string with a post code NN2 7DG. SQL Server can perfectly find 7DG but not NN2.
My stop words list is empty.
I looked at the indexed keywords and NN2 is not even there, while 7DG is
-- list ...
- 745
2
votes
1
answer
57
views
Efficient way to execute batch of full-text queries on set?
Just trying to chip away at some bottlenecks in our process (that existed long before I got here).
There's a part of the process where we try to categorize input based on a set of full text queries (...
- 207
1
vote
0
answers
118
views
MySQL - Too many nested sub-expressions in a full-text search
I have a full-text search query with many sub-expressions like this:
SELECT DISTINCT id
FROM search_table s
WHERE MATCH (s.search) AGAINST ('+(failure* protection*) +(math* police*) +(insect* hall*) +(...
1
vote
0
answers
575
views
How to consider special characters in ts_query and ts_vector as plain english
How to include special characters &, *, / in to_tsquery and to_tsvector . It converts boyce & doyl as {boyce:1 , doyl:1}. How to treat them as char so they can be searched in a query ?
- 11
2
votes
1
answer
520
views
Imporve MySQL InnoDB Full-text Search Performance
1.Background:
DB: MySQL 8.0, InnoDB engine;
Table size: About 2M rows of 2G data; FULL_TEXT index column: sentence (TEXT data type) https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
Old ...
- 21
0
votes
1
answer
15
views
why the full text search still slow after install the zhparser
I have already installed the zhparser to split the chinese words, then I using this sql command to do a full text search:
SELECT *
FROM article a
WHERE to_tsvector('dolphinzhcfg', title) @@ ...
- 755
4
votes
2
answers
2k
views
How to store and query strings matching prefix or suffix in PostgreSQL?
Say I have 1 million words each in 100 different languages (so 100 million words, each with alphabets ranging from 10 characters to 200 characters, although Chinese has 80k "characters" ...
- 189
2
votes
2
answers
798
views
How to remove duplicates and sort in a subquery?
I have records with a "title" column that I am splitting up by space and performing a full text search with each word. I am storing the result in a materialized view.
This works, but I get ...
- 123
1
vote
0
answers
409
views
MySQL match against from different tables
Compositions of tables is like:
table
id
name
id_table2
1
test
10
23
row
44
table2
id
name
10
Bob
44
another row
table3
id
name
id_table2
77
Zotty
10
90
Solo
44
Composition is basically ...
- 111
-1
votes
1
answer
1k
views
LIKE query with leading wildcards in large MySQL production database
According to insights in Amazon RDS the following query in a large MySQL production database is causing high loads ( ~ 50.000.000 entries):
SELECT * FROM entities WHERE status='ready' AND user_id='...
- 215
0
votes
1
answer
344
views
Searching against a large list of people at the same time in Postgres
I have a list of people that varies between 2-5k entries that has this general format:
firstname1 lastname1, firstname2 lastname2, firstname3 lastname3 ...
I have a user_table which contains about 250-...
- 17
0
votes
0
answers
110
views
Optimizing queries that involve rank computing PostgreSQL full-text search
Suppose we have a MATERIALIZED VIEW that has to be searched using PostgreSQL full text search. View contains both fields with information about the object and precompiled (and annotated with weigths) ...
- 101
3
votes
1
answer
374
views
How to conclude whether the full text service is installed and/or used?
SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')
Above query returns 0
However, the below query returns 1 for all user databases. How to conclude whether the full text service is installed and/...
- 3,442
3
votes
0
answers
227
views
SQL Server Full Text search for alphanumeric string
I have encountered an odd behavior with SQL Server Full Text Search, If search term is character followed by numbers or numbers followed by character it is returning both as match although one is ...
- 39
0
votes
1
answer
1k
views
Oracle Text query with CONTAINS operator and wildcard on stopword does not find results
Why does the Oracle Text CONTAINS query operator not return any results when a wildcard is applied on a stopword?
Example:
(Those sample values assume that an english stoplist is used - that depends ...
- 85
2
votes
0
answers
194
views
Full text index search with character at the beginning
I am looking at tables like this (I did not design them):
CREATE TABLE EventIDsToSearch (id int not null)
insert into EventIDsToSearch values (1)
insert into EventIDsToSearch values (92)
insert into ...
- 503
1
vote
0
answers
387
views
How to find root cause for SQL full text crawl error 30059?
Doing several FT index rebuilds after upgrading SQL server and one the FT index crawls died with error 30059 but not much else for root cause. I can resume the population attempt but would like to ...
- 1,087
1
vote
0
answers
135
views
SQL Server oddity with full-text indexing and case sensitivity and numbers
This problem may be unique to our server, but I can't tell from the symptoms where the issue may lie.
My SQL Server version is Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 ...
- 11
0
votes
0
answers
429
views
InnoDB - Fulltext (match against) extremely slow, wrong indexes?
I've recently migrated my myisam-tables to InnoDB and some things are a bit weird for me. For starters, the indexes looks a bit different (and maybe is the reason why the new InnoDB-implementation is ...
- 121
1
vote
1
answer
1k
views
Full text search can not find incomplete words
I am using sql server 2019. I created a full text index based on the example of Microsoft, but there is a problem, when part of a word is searched in the text, there is no result
sql server version:
...
- 21
1
vote
0
answers
173
views
FT CRAWL on SQL Server 2012 holds up too many connections
We are facing an issue with Full-Text service on our SQL Server 2012, it seems that it's behind crawling several dbs and once it's running it holds up too many connections, I can confirm that with ...
- 11