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.

Filter by
Sorted by
Tagged with
-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 (...
Amar's user avatar
  • 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 (...
Alexander Dmitriev's user avatar
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 ...
AndyD273's user avatar
  • 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 ...
lellefood's user avatar
  • 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 ...
Tyler's user avatar
  • 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 "'...
Ping S's user avatar
  • 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 ...
JoeCharles's user avatar
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 ...
user45867's user avatar
  • 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 ...
adam drobniczak's user avatar
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 ...
Basj's user avatar
  • 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 ...
Kevin Huntly's user avatar
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 ...
Peter's user avatar
  • 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 ...
ben's user avatar
  • 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' ...
jay's user avatar
  • 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 ...
ennui's user avatar
  • 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 ...
Oromis's user avatar
  • 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 ...
Przemysław Łukawski's user avatar
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-...
Jac Frall's user avatar
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( ...
UNeverNo's user avatar
  • 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 ...
123mig's user avatar
  • 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 ...
ennui's user avatar
  • 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 ...
Jimmison Johnson's user avatar
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 ...
ennui's user avatar
  • 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 ...
Cerin's user avatar
  • 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 ...
olivieradam666's user avatar
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 ...
DaringAardvark's user avatar
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 ...
Eric's user avatar
  • 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 "...
Trigve's user avatar
  • 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....
Jason's user avatar
  • 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 ...
jitbit's user avatar
  • 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 (...
user1664043's user avatar
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*) +(...
But those new buttons though..'s user avatar
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 ?
sahil's user avatar
  • 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 ...
123mig's user avatar
  • 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) @@ ...
Dolphin's user avatar
  • 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" ...
Lance's user avatar
  • 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 ...
Jared's user avatar
  • 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 ...
tidpe's user avatar
  • 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='...
Blackbam's user avatar
  • 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-...
thesofaking's user avatar
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) ...
Dmytro's user avatar
  • 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/...
variable's user avatar
  • 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 ...
Kashif moin's user avatar
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 ...
cremor's user avatar
  • 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 ...
w0051977's user avatar
  • 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 ...
user2368632's user avatar
  • 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 ...
user1693404's user avatar
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 ...
Oakleaf's user avatar
  • 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: ...
AAA's user avatar
  • 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 ...
Arash Pakbaz's user avatar

1
2 3 4 5
13