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.
615
questions
1
vote
1
answer
35
views
Why does ORDER BY significantly slow down my query with a computed score of trigram similarity and null field?
I'm working on optimizing a query in PostgreSQL, and I've encountered a performance issue when using the ORDER BY clause. The query is intended to search profiles based on a similarity match to a name ...
0
votes
1
answer
26
views
How to find similar articles with FTS-vectors in postgres?
I have a table, that contains a text-column "title" and an autogenerated column "fts_title", that contains the tsvector-based values for the original values, pre-generated and ...
- 101
0
votes
1
answer
41
views
What is the minimum level of DBCC CHECKDB needed to check full-text indexes?
I have a database containing an unreasonably large full-text index. DBCC CHECKDB WITH PHYSICAL_ONLY takes about 25 minutes on this database, but DBCC CHECKDB WITH DATA_PURITY, EXTENDED_LOGICAL_CHECKS ...
- 645
0
votes
0
answers
21
views
How to Create a Case-Insensitive Search Index for Multiple Fields in MongoDB Atlas Search
I'm working with a MongoDB collection named skus and need help creating a search index and query for case-insensitive search across multiple fields with priority. Here's a sample document from the ...
0
votes
0
answers
30
views
Adding a new fulltext column results in degraded performance
I have a search table with a schema something like this:
CREATE TABLE `product_search` (
`product_id` int NOT NULL,
`search` text NOT NULL,
PRIMARY KEY (`product_id`),
FULLTEXT KEY `search` (`...
- 726
0
votes
0
answers
92
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 ...
- 101
0
votes
1
answer
94
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 | ...
- 103
0
votes
0
answers
32
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 (`...
1
vote
0
answers
40
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 &...
- 111
3
votes
5
answers
963
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',
...
- 563
0
votes
0
answers
27
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 ...
- 645
0
votes
1
answer
50
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 ...
- 645
0
votes
0
answers
201
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....
- 645
0
votes
0
answers
62
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?
- 1,024
5
votes
1
answer
128
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);...
- 153
1
vote
2
answers
726
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-...
- 645
1
vote
1
answer
366
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 ...
- 15
-1
votes
1
answer
48
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
149
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
186
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 ...
- 215
0
votes
1
answer
600
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 ...
- 103
0
votes
1
answer
260
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
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 ...
- 25
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
...
- 1,693
0
votes
1
answer
307
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
268
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 ...
- 171
1
vote
0
answers
61
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
1
vote
1
answer
377
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
430
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' ...
- 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 ...
- 103
1
vote
1
answer
222
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
1
answer
353
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
2
votes
0
answers
160
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(
...
- 117
2
votes
1
answer
176
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
39
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
417
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
245
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,385
1
vote
0
answers
127
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
1k
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
402
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
306
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
245
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 ...
- 765
2
votes
1
answer
64
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 (...
- 235
1
vote
0
answers
171
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*) +(...
- 726
1
vote
0
answers
807
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
699
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
19
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) @@ ...
- 815
5
votes
2
answers
3k
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" ...
- 211
2
votes
2
answers
969
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