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
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) ...
user avatar
  • 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/...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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: ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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&...
user avatar
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 ...
user avatar
  • 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 ('&...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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('...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
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 ...
user avatar
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` ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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`) ...
user avatar
  • 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&...
user avatar
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(...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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, ...
user avatar
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; ...
user avatar
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, ' ',...
user avatar
  • 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 ...
user avatar
  • 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%'. ...
user avatar
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 ...
user avatar
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 ...
user avatar
-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 ...
user avatar
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:...
user avatar
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 ...
user avatar
  • 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 ...
user avatar

1
2 3 4 5
12