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
vote
1answer
121 views

How to read Mongodb entries in plaintext? [duplicate]

I'm testing mongodb on a local machine for the first time, and I just want to see the actual plaintext contents of a database entry. Started the mongo console: $ mongo Set dbs: > use <my dbs ...
1
vote
1answer
760 views

How to get lexemes array from Postgresql ts_vector, sorted by lexems positional information?

Postgresql full text search to_tsvector() function returns a sorted array of lexemes. I need exactly the same function, but returning unsorted array of lexemes. Is there any such postgresql function ...
5
votes
2answers
2k views

How can I implement full-text search on a single value in a JSONB column?

I have a table that stores a conversation between two people. The data will look something like this: CREATE TABLE foo AS SELECT $$[ { "user": 1, "timestamp": 1, "message": "First message" }, ...
0
votes
2answers
333 views

Can i set Accent insensitive for individual languages?

Can i make a table/column like this: Accent sensitive for Turkish and English. Accent insensitive for Arabic. Actually if this can be done on a full-text index, that would also work for me. Accent ...
1
vote
1answer
82 views

How to match similar records using multiple columns

Given a GUID for an event, say logins, how might I search for similar or dissimilar events, utilizing the other columns data? For example: Find me similar logins to [GUID] {Server:"s123", State="NY", ...
4
votes
0answers
331 views

Should Full Text Filter Daemon Service be a cluster resource?

Our newly installed and configures Sql Server Always On Availability Failover Cluster with Sql 2016 Enterprise Edition with four instances and six nodes. It includes the DB Engine Service and the ...
0
votes
1answer
824 views

Create and use Fulltext Index in a Stored Procedure

I want to create and use a fulltext index within a single procedure. For example: CREATE PROC [dbo].[dbp_PCL_to_Demo_Matching_Mason] AS --IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE ...
2
votes
1answer
797 views

MySQL use both FULLTEXT and normal index

I have a table like this: id type idElem name date content lang state ------ ------ ------ ------ ------ ------- ------ -------- When I run this query against it: ...
1
vote
1answer
773 views

What is the meaning of the segment name of a domain index?

I want to know the space used by one of my indexes. I query the view DBA_SEGMENTS to do it: SELECT segment_name, sum(bytes) FROM dba_segments WHERE segment_name LIKE '%INDEX_NAME%' GROUP BY ...
3
votes
1answer
818 views

Postgres full text search with better match

I have this scripts for full text searching in my postgres database: function for unaccent concat: CREATE OR REPLACE FUNCTION f_concat_ws_unaccent(text, VARIADIC text[]) RETURNS text LANGUAGE sql ...
1
vote
1answer
59 views

Search 200M documents of text by keyword in text

Data ~5M documents, would love to have ~223M, with the text of ~10-1000 words each. It must be utf8 and support any spoken language. All words in a text are unique and I can also order them or do ...
5
votes
3answers
4k views

Full text search over multiple related tables: indices and performance

We have the following database structure CREATE TABLE objects ( id int PRIMARY KEY, name text, address text ); CREATE TABLE tasks ( id int PRIMARY KEY, ...
1
vote
1answer
2k views

Postgres pg_trgm JOIN multiple columns with large tables (~50 million rows)

I'm trying to perform a query on Postgres using the pg_trgm extension that attempts to fuzzy match across multiple columns in two tables. I've let the query run for an hour or more and it hasn't ...
0
votes
1answer
104 views

improve MySQL text searches

What MySQL settings improve text searches? Which resources (memory, cpus, disk: ssd) to add first to increase performance? Setup Amazon RDS type: db.t2.medium (2 CPU; 4G mem) MySQL 5.6.27 ...
1
vote
1answer
217 views

What causes temporary slow (fulltext) queries?

I logged this slow query on a 400k rows MySQL 5.6 InnoDB table: SELECT SQL_NO_CACHE topic_id FROM topics_search WHERE host_id = 1 AND forum_id IN (23, 50) AND MATCH(topic_text)...
6
votes
2answers
7k views

PostgreSQL full text search on many columns

I need an advice with searching of record based on specified string. Search strings can contain values from these columns. Values in this string don´t have to be strictly identical given in the ...
2
votes
1answer
540 views

Can SQL Server full-text search tokenize words within words?

Suppose I have the following schema and data in SQL Server 2012: create table Exclamations ( ID int not null primary key identity(1,1), Exclamation nvarchar(150) not null ); insert into ...
0
votes
1answer
343 views

Full-text search on column for existence of a keyword from a table

I have a table of keywords and I want to query another table and return rows where a column contains at least one keyword. Is it possible query like that using full text indexes? If not is there a ...
3
votes
2answers
704 views

How can I implement full-text search on a single value in a text[] column?

I am using Postgresql 9.3 on an Ubuntu 12.4 machine. I've only recently started studying database and database design, and I am to create a database that includes text that will later be searched ...
5
votes
3answers
4k views

UPDATE with join condition on matching words in columns of another table

I have 2 tables that looks like this: Table A: CREATE TEMP TABLE table_a ( Column_1 text, ID_number int ); INSERT INTO table_a VALUES ('foo,bar,baz', 123), ('qux,quux,quuz',456), ('corge,...
1
vote
1answer
724 views

FULLTEXT CONTAINS search returns no results in SQL SERVER 2014 with multiple search terms

When I try to use multiple search terms in a CONTAINS clause I get no results. SELECT * FROM dbo.Address WHERE CONTAINS([Address].*,'"Detroit"') AND CONTAINS([Address].*,'"48226"') OPTION(...
0
votes
1answer
874 views

How can I change the value of min word length for specific query in fulltext search?

I can set it a new value by adding this line under the [mysqld] section in my.cnf configuration file: ft_min_word_len = 3 // or any other length But I don't want that. I want to know, can I do that ...
5
votes
0answers
4k views

Postgres 9.6.1 Full Text Search dictionaries for most spoken languages

Using Mac OSX Yosemite 10.10.5, I am trying to run full text search operations, such as to_tsvector, to_tsquery, etc and have a need for dictionaries in about 80+ languages. Postgres seems to only ...
1
vote
3answers
1k views

Using fulltext search to find a sequence of digits

We have a column of ten digit numbers (with leading zeros). We are trying to find rows that have a specific sequence of digits. For example, all rows containing "5088" in any place in the column. ...
2
votes
0answers
485 views

FTS non-latin: text-search query contains only stop words or doesn't contain lexemes, ignored

I'm trying to use Full Text Search with Chinese characters using a postgres (9.6.1) FTS extension ZHParser. Following some of the examples here, I wanted to select my title column and match the title ...
1
vote
3answers
835 views

How to store short stories for access to individual sentences?

I am building a database for the first time ever (using PostgreSQL), and am very conflicted over the most efficient/logical way of storing a body of text (aka, a story). The conflict stems from the ...
1
vote
1answer
3k views

SQL Server Full-Text Index crawl: Is it done or not?

I am on SQL Server 2012. I have one table with one full-text index. That index was dropped and recreated around 8am due to a change in the datatype of the indexed column from TEXT to VARCHAR(MAX). ...
1
vote
0answers
802 views

How long does it take a full text index to update after inserting a large amount of text?

I'm trying to track down an issue where some searches do not return a hit but do return a hit after some time. Process. We currently grab some 150,000 pages of text and insert them into our database. ...
0
votes
1answer
287 views

SQL Full Text Search - Result Order changing

I am using Full Search text to implement search functionality. Basically I will have to do search on all the columns with the search string. Search string will have combination of values from ...
0
votes
1answer
324 views

multiple MYSQL full text indexing for innodb and memory + disk efficiency

I have the following table: (Create table statement) CREATE TABLE `phppos_people` ( `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(255) COLLATE utf8_unicode_ci ...
13
votes
1answer
11k views

Get partial match from GIN indexed TSVECTOR column

I would like to get results by query this: SELECT * FROM ( SELECT id, subject FROM mailboxes WHERE tsv @@ plainto_tsquery('avail') ) AS t1 ORDER by id DESC; This works and return rows with tsv ...
1
vote
1answer
261 views

Order by exact matches (jsonb array), then lexeme similarity

Say I have a table like this in Postgres 9.5: CREATE TABLE public.posts ( content text, tags jsonb, ) I'd like to design a single query that: Finds results based on exact tag OR matches (...
2
votes
1answer
828 views

how innodb_ft_result_cache_limit work?

Quote from MariaDB Documents Description: Limit in bytes of the InnoDB FULLTEXT index query result cache per fulltext query. The latter stages of the full-text search are handled in memory, and ...
2
votes
0answers
290 views

Create PostgreSQL fulltext Parser

I want to make a FTS on my text column on Postgres, the problem is that I want to search words with "_" between letters, like "the_event" but the default parser split those words and identify "_" like ...
1
vote
0answers
62 views

System Stoplist empty after installing SQL Server 2016 Express Edition SP1

I recently installed SQL Server 2016 Express Edition and then applied SP1. But I see my system stoplist empty for fulltext search. Select * from sys.fulltext_stoplists; This does not return any ...
2
votes
1answer
454 views

Pragmatic approach to k-means clusters with PostgreSQL

I am looking for a super simple and very pragmatic approach to k-means clustering of questions using PostgreSQL database alone. While I am fully aware that this method may not yield meaningful ...
1
vote
0answers
82 views

Full text search with substrings

I have a table of about ~10M rows, each contains a (unique) sentence. I'm looking for ways on how to be able to offer fast full-text search that's also supports substrings, prefixes and suffixes, ...
3
votes
1answer
1k views

PostgreSQL full text search tsv column trigger with many-to-many

I have a tsv column on my campaign table and I also added a trigger to update it every time the row changes. Tsv is currently constructed from a few table columns. The table also has multiple tags via ...
0
votes
1answer
251 views

T-SQL Fulltext search by first word

How can I perform "starts with" logic with full text search? For example there is a Contains logic : WHERE contains(Note,' "*word*" ') I guess I need search by first word in the string, but how can ...
1
vote
1answer
135 views

Index a field containing people's names

I have a materialized view of around 200 million records and would like to add an index to the 'full_names' field to speed up the search. The name data is stored like this; | full_names ...
4
votes
2answers
2k views

Why is full text index falling behind?

I have a full-text index set up on a table; the table is under fairly constant load, receiving ~50k inserts and ~35k deletes daily, with rather short (up to 5 minutes) gaps between inserts. The index ...
3
votes
3answers
1k views

PostgreSQL GIN index not used when ts_query language is fetched from a column

I've got a table that stores some multilingual content: CREATE TABLE search ( content text NOT NULL, language regconfig NOT NULL, fulltext tsvector ); CREATE INDEX search_fulltext ON search ...
3
votes
1answer
213 views

FreeTextTable - Query as a set operation vs. having to cursor

Does anyone know if it is possible to join to a free text table to query for many search terms in one operation vs. having to cursor over? For example if I have a function that gets the top 1 closest ...
2
votes
1answer
128 views

Add full text index facility to SQL Server instance

Can anyone help me to add full text index facility to a SQL Server instance? FYI it is cluster environment, SQL Server 2012 Developer Edition. Running select serverproperty('isfulltextinstalled') ...
2
votes
1answer
932 views

Supporting non-ascii characters in Postgres full text (english)

We're implementing Postgres full-text search. While the bulk of the text is normal English, there are a number of place names which include diacriticals (ñ,á,ó, etc) More specifically, there are ...
3
votes
1answer
3k views

SQL Server Full-text crawl log location

This might be a silly question, but I can't for the life of me figure out where the crawl logs for my SQL Server 2012 instance are. TechNet says to look under %ProgramFiles%\Microsoft SQL Server\...
4
votes
0answers
418 views

PostgreSQL full text search: How to change what classifies as a “word separator”?

Using ts_debug I can see what symbols Postgres treats as (what I call) a "word separator". Example: SELECT ts_debug('english', 'Hello. ABC') Result: (asciiword,"Word, all ASCII",Hello,{...
3
votes
1answer
3k views

How to make a FULLTEXT search with ORDER BY fast?

I'm trying to get a simple FULLTEXT match to be faster when using order by on another column on a table with over 100 million rows. The basis is one table with a fulltext on two columns and I want to ...
7
votes
3answers
2k views

Finding the most commonly used non-stop words in a column

Simply put, I have a Postgres column containing ordinary prose and would like to determine the x most commonly used words ("word" being a group of characters delimited by a space, but not being stop ...
2
votes
2answers
651 views

How can I perform a full-text CONTAINS call across a list of phrases?

I have a list of words/phrases in a table phrases. I have another table with a full-text catalog indexing the description column. I want to populate a table results with a record for each phrase, and ...

1 2 3
4
5
10