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.
574
questions
0
votes
0
answers
5
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) ...
- 1
3
votes
1
answer
72
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/...
- 2,109
0
votes
0
answers
29
views
Oracle 19c-ee CTX_REPORT.INDEX_SIZE and CTX_REPORT.INDEX_STATS cannot be executed
We created a simple search index, based on a 19c-ee instance of the official Oracle Docker images, with the following script:
CREATE TABLE EVT (
ID VARCHAR2(36) NOT NULL,
EVENT BLOB NOT ...
- 11
3
votes
0
answers
63
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
0
answers
86
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 ...
- 65
0
votes
0
answers
16
views
How has sharding become easier in mysql now(in 2022) as compared to before?
I was currently implementing a side project where I had to make a choice for database. The basic considerations are listed below:
The schema is not strict and could have many empty fields.
There ...
2
votes
0
answers
33
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 ...
- 493
1
vote
0
answers
48
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 ...
- 922
1
vote
0
answers
46
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
30
views
Full Text Index Crawler - MAXDOP - FT_IFTS_RWLOCK
We ran into a rather interesting situation regarding our Server and it's Full Text Catalog yesterday. I'm wondering if the experts here can shed some light on it as I've not been able to find ...
- 7,491
0
votes
0
answers
49
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
0
votes
1
answer
59
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:
...
- 11
1
vote
0
answers
26
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 ...
1
vote
1
answer
62
views
Fulltext search using query expansion in MS-SQL
I am using fulltext search and using query expansion mode in MySQL.
Query expansion works like this: the most relevant words found with your initial search term are added to your initial search term ...
- 111
0
votes
2
answers
149
views
What is the difference between tsvector_update_trigger and ts_vector generated column?
Newbie to full text search, I have a feed_items table as follows
feed_items (...title varchar, summary varchar, fullarticle varchar,...)
I want to create a ts_vector column to search query from ...
- 246
3
votes
1
answer
73
views
Why tsquery containing a hyphen isn't found in full text search?
Trying to figure out, why the below query isn't returning any results:
select id from story where tsv_story_text @@ to_tsquery('CumEx-Files<->2.0');
The term exists in the headline of a story ...
- 133
2
votes
0
answers
90
views
SQL Server full text indexing shows considerable increase in logical IO after upgrade from 2008 R2 to 2012
Summary
I have two vanilla SQL Server instances running on identical (Win2012) VMs, one is SQL Server 2008 R2, the other is SQL Server 2012. I run the same DDL script on each to create a single DB ...
- 21
0
votes
2
answers
96
views
OOM errors when running queries that have a boolean fulltext search?
I am experiencing intermittent oom errors when running queries that have a boolean fulltext search in them.
I am on AWS Aurora (t2.medium).
Here is an example query, considering the table "sets&...
- 117
2
votes
1
answer
28
views
Configuring space symbols in text search configuration
I'm using the following text search configuration:
CREATE TEXT SEARCH DICTIONARY tsd
(TEMPLATE = pg_catalog.SIMPLE);
CREATE TEXT SEARCH CONFIGURATION tsc_test
(COPY = german);
ALTER TEXT SEARCH ...
- 123
0
votes
0
answers
73
views
Is it possible perform a fulltext search on MySQL 8.0, with double quotes and wildcard?
Based on past answers, I see that it was previously possible to perform fulltext searches with double quotes and wildcard, like the following:
SELECT COUNT(*) FROM mytable MATCH (mycolumn) AGAINST ('&...
- 327
2
votes
1
answer
91
views
How to rebuild Full Text indexes after MSSQL upgrade from 2014 to 2017
When I updated from 2014 to 2017 I chose the option to migrate Full-Text Indexes instead of rebuild them during the update. The update went well, and I'd like to repopulate those indexes now, but I'm ...
- 21
0
votes
0
answers
73
views
JSON Search index usable with 'LIKE' but not with '='
We are seeing an issue with Oracle 19c and a JSON search index.
Given:
A table GEO containing a column JSON_DATA with JSON data and an IS_JSON constraint
An index created like this: create search ...
- 101
0
votes
0
answers
22
views
Oracle Text truncate words to 64 CHARS but token limit is 64 BYTES
I'm using Oracle 19c.
I have a table with a VARCHAR2(4000) column for which I'm creating a full-text index using Oracle Text.
The SQL creating the index is as follow:
begin
ctx_ddl.create_index_set('...
0
votes
0
answers
20
views
Full Text Search Launcher service and process
I am puzzled by the full text search in sql server. I am trying to break down the issue in multiple sub questions, maybe that would help to bring some light into the situation.
I created an ...
- 820
0
votes
1
answer
270
views
Difference between unnest() in SELECT vs unnest() in FROM on Postgres
I want the lexemes of a tsvector array placed in a column, one per row. I can first unnest the array and then the resulting tsvectors with:
SELECT (unnest(unnest(my_array))).lexeme
FROM my_table
...
- 1,077
0
votes
1
answer
99
views
MySQL table + partitioning + spatial data
We have a table that currently has every postal address for a single state, currently around 13 mil records. Among postal addresses, it also contains spatial coordinates for each address along with ...
- 146
0
votes
0
answers
21
views
Would filtering by order ID from nvarchar(MAX) column benefit from fulltext search?
In my table I have a nvarchar(MAX) column which holds data about our orders in XML form. Another department often has to get information from the table by, most commonly, looking for a certain order ...
- 1
0
votes
0
answers
18
views
Are FullText catalog indexes managed by Azure SQL?
I know normal indexes are maintained (reindexed) automatically on azure SQL. (without the need of having to create a jschedule job to run it periodically)
However, non of the documents are clear to me ...
- 201
0
votes
1
answer
36
views
MySQL FULLTEXT BOOLEAN Query result differs between identical DBs
I have two Windows MySQL 5.7 InnoDB databases. One is a production db, and the other is a test db that was created from an image of the prod db. Both have equal data loaded from an Oracle merge script....
- 111
0
votes
1
answer
92
views
Upgrade to Mysql 8 or MariaDB 10.3 for InnoDB Fulltext searches?
I'm upgrading a Mysql 5.7 server and have the choice to move to Mysql 8 or MariaDB 10.3.
The by far most important function in this database server is InnoDB fulltext search which I haven't found ...
- 142
0
votes
0
answers
22
views
Reduce mysql query ( Fulltext-search ) execution runtime, server-side?
VPS:
ubuntu
Django (latest)
Python3.6
Mysql
Structure :
CREATE TABLE `tablename` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET latin1 NOT NULL,
`wallpaper` ...
0
votes
1
answer
29
views
Can the Oracle-Text OR be replaced with a comma?
I have incorporated user-accessible Oracle-Text functionality within my application and I noticed that the comma , can be used in place of OR or the pipe | character.
Searching on 'red OR green OR ...
- 105
0
votes
2
answers
134
views
Full text search partial search with 'in:*' not working
select to_tsvector('english', 'JAVA') @@ to_tsquery('english','ja:*');
select to_tsvector('english', 'india') @@ to_tsquery('english','in:*');
The first one is returning true while the second is ...
0
votes
1
answer
276
views
MySQL FULLTEXT search (match against) not working
CREATE TABLE `test` (
`id` int(11) NOT NULL COMMENT 'unique id',
`text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `text`) ...
- 101
0
votes
1
answer
25
views
How can I make an index covering a ts_vector expression and also an integer?
I am using the ruby pg_search gem, where code like this: Food.search("orange") produces a query like this.
SELECT "foods".*
FROM "foods"
INNER JOIN
(SELECT "foods&...
- 475
0
votes
1
answer
417
views
Find partial match with textsearch query
I have the following query that I did hard looking on this site:
SELECT *
FROM renns
WHERE
(
to_tsvector('english', coalesce(numar_admin, '')) ||
to_tsvector('english', coalesce(...
- 101
0
votes
1
answer
58
views
Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance
In the official documentation of Boolean Full-Text Searches:
Boolean full-text searches have these characteristics:
They do not automatically sort rows in order of decreasing relevance.
However, ...
- 7
-2
votes
2
answers
112
views
Search with more than 5 Million recods With Posgres
Search to be implemented for currently developing a project which is being developed using Spring Boot and Postgres.
The search table has at least 8 columns, all the columns are used in the search ...
1
vote
1
answer
95
views
to_tsquery - Does it always accept quert text?
I am very new to FTS features in Postgres, in fact, have started exploring them since yesterday only.
I wanted to pass list of values stored in columns of a DB tables instead of query text in ...
- 11
1
vote
0
answers
714
views
Faceted search with PostgreSQL
In our app, we are building a search for real estate listings.
There are a number of search types:
1. Based on predefined parameters
This one is pretty straightforward and achievable with a simple, ...
1
vote
1
answer
246
views
MariaDB FULLTEXT search with short / mandatory words
Just checking I'm understanding this correctly:
CREATE TABLE customer (
id INT NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
PRIMARY KEY (id),
FULLTEXT (name)
) ENGINE = InnoDB;
...
- 125
1
vote
0
answers
9
views
use dynamic Text in contains function
I have a query to find something in a long text by a dynamic parameter
this is my query:
declare @searchTerm nvarchar(100)
set @searchTerm='Product/IndexShowProduct/'
select
REPLACE(product.name, ' ',...
- 21
0
votes
1
answer
236
views
full text phrase-search with stop word
I would like SQL Server to search a phrase "centos 8" for example. The exact match.
But since number "8" is considered a stop word, it only searches for "centos".
Even if ...
- 635
0
votes
0
answers
69
views
Is instant text search possible in big table with HTMLs?
I have a pretty big table with HTMLs (about 500K rows, average length of text is ~150K).
It is required to make fast search (less than 1s) rows which match let say '%<meta name="twitter:app%'.
...
0
votes
1
answer
90
views
Mysql: [match() against()] full text search order full field value match first
I am doing a full-text search against a WordPress database, I am using match() against() to test the search term against multiple fields from multiple tables, all is working great, but I want to ...
2
votes
1
answer
380
views
How to deal with SQL Server Full Text Log Files?
We use SQL Server full text search functionality. As a result we have lots of full text log files like SQLFT0003200286.LOG.5 in the sql server error log folder. Is there anything we can do to ...
- 922
-1
votes
1
answer
195
views
Need to filter by month
I am using SQL Server Management Studio.
I am trying to filter by month using dtm DATETIME as my format XX/XX/XXXX. I am very new to SQL, so I was trying to just filter by the month of January 01 with ...
- 11
0
votes
1
answer
100
views
Match Against search not doing exact search?
I'm using MariaDB version 10.2.22 with a InnoDB engine, utf8 charset, it has one column that is indexed FULLTEXT. The table itself is only 4.4 GB with avg row length at 255. When I do a query such as:...
- 11
1
vote
2
answers
493
views
MariaDB (MySQL) slow query when primary key range combined with fulltext index
I've a table described below, with two columns - integer primary key and title text - currently holding circa 3 million records. As seen in the metadata below, there's a BTREE index on integer primary ...
- 135
1
vote
1
answer
168
views
Safe values for InnoDB variables when using FULLTEXT searches
I'm using Maria DB, version 10.2.22, where one database column uses FULLTEXT for a broad document searching. However, I've ran into a "Table handler out of memory" on some searches. The ...
- 11