Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
0 votes
0 answers
30 views

Postgres SQL tsvector case sensitive search for full text search

Currently I am using tsvector function of postgresql for full text search. But by default all word are converted to lower case and matches and all query are performed using case insensitive match. But ...
The6thSense's user avatar
0 votes
1 answer
53 views

Full Text Search on Postgres jsonb column [closed]

So I have an alert table like this: my_db=> \d alerts_alert Table "public.alerts_alert" Column | Type | Collation | ...
Hayk Davtyan's user avatar
0 votes
0 answers
25 views

Why my fulltext search fails to return values?

I am experementing upon fulltext search using the following example: CREATE TABLE `search_demo` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`...
Dimitrios Desyllas's user avatar
1 vote
0 answers
30 views

Does PostgreSQL trigram search support collations?

I'm building a trigram search using PostreSQL extension pg_trgm. I want the search to be case insensitive and to ignore accents. I've tried to achieve that by using this collation: CREATE COLLATION &...
Draex_'s user avatar
  • 111
3 votes
5 answers
845 views

Is COMPACT a better format for fixed length rows than DYNAMIC?

In MySQL / MariaDB I have this table with fixed-length rows (no VARCHAR, TEXT, etc) CREATE TABLE trigram ( id BIGINT(20) NOT NULL, trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci', ...
O. Jones's user avatar
  • 563
0 votes
0 answers
26 views

Purely in performance terms, how do CONTAINS([MyCol], 'foo') and [MyCol] LIKE '%foo%' compare?

Consider CONTAINS([MyCol], 'foo') and [MyCol] LIKE '%foo%'. Assume the best possible indexes on [MyCol], whatever those may be (at minimum, we need a unique key and a full-text index). I know that the ...
J. Mini's user avatar
  • 431
0 votes
1 answer
47 views

Is it possible to hit a full-text index without using a function specifically designed for them?

There are several functions that are specifically designed to hit full-text indexes. I think that the following is a complete list: CONTAINS CONTAINSTABLE FREETEXT FREETEXTTABLE Is it possible to ...
J. Mini's user avatar
  • 431
0 votes
0 answers
130 views

In the past week, SELECT StatMan [...] has run 150,000 times. Is this normal?

I retain a week's worth of Query Store data. I was inspecting it for one database earlier today and found something surprising. This query SELECT * FROM sys.query_store_query AS qsq INNER JOIN sys....
J. Mini's user avatar
  • 431
0 votes
0 answers
36 views

How to stop full-text index population?

Oops! I managed to trigger population of all full-text indexes in all databases! They now hog the CPU and all the threads for themself! How do I stop them?
Yano_of_Queenscastle's user avatar
5 votes
1 answer
104 views

Improving FTS phrase search on postgres for common terms

I have a table defined like CREATE TABLE details_search ( id int4 NOT NULL PRIMARY KEY, "search" tsvector NULL ); CREATE INDEX details_search_idx ON details_search USING gin (search);...
g3rv4's user avatar
  • 153
1 vote
2 answers
357 views

How can I tell if a full-text index is being used?

It's possible to tell how much a rowstore index is used, but what about full-text? I could find nothing in any GUI or DMV. Even dbatools doesn't support it. I've inherited a server that has a few full-...
J. Mini's user avatar
  • 431
1 vote
1 answer
141 views

Best Practices for searching SQL Server NVARCHAR col using WHERE LIKE wildcard term

I have a pretty simple table SQL Table (currently Azure SQL, may stay there or end up in local SQL Server 19 instance if that matters), about 100,000 records, that's mostly for reading but will ...
mcsov's user avatar
  • 15
-1 votes
1 answer
41 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
65 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
127 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
485 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
  • 103
0 votes
1 answer
165 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
24 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
1k 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
2k 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,683
0 votes
1 answer
220 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
225 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
  • 171
1 vote
0 answers
50 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
1 vote
1 answer
289 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
335 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
  • 31
0 votes
1 answer
1k 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
204 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
1 answer
286 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
2 votes
0 answers
130 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
  • 121
2 votes
1 answer
136 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
37 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
373 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
202 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,365
1 vote
0 answers
126 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
985 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
360 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
252 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
227 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
  • 765
2 votes
1 answer
58 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
152 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*) +(...
Eaten by a Grue's user avatar
1 vote
0 answers
702 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
616 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
16 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
  • 775
5 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
  • 209
2 votes
2 answers
906 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
445 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
2k 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
432 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
112 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

1
2 3 4 5
13