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.

learn more… | top users | synonyms

0
votes
1answer
13 views

Fulltext Search Doesn't Match a Hashtag

We maintain a table that looks like this to perform search queries on our forums database. I'll draw your attention to the FULLTEXT columns: CREATE TABLE `forums_posts_search` ( `post_id` ...
3
votes
0answers
36 views

SQL Server 2014 - Full text index never stops populating

I have a server that is running SQL Server 2014. I have full text indexes on some of my tables in my databases. The problem is that it's always saying populating and it never finishes. So my queries ...
1
vote
0answers
25 views

PostgreSQL trigram GIST vs. GIN indexes

I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes. Initially, I started to use GIN ...
4
votes
1answer
27 views

Update queries slower after enabling SQL Server Full-Text Index

I have an asp.net website with many insert, update, and delete queries running against my database. A few days ago I create a Full-Text Index on two columns of one of tables. After that I realized ...
0
votes
0answers
28 views

mysql MATCH relavancy score

I thought this would be an easy question to google for; but I couldn't not find it. Given the following query: SELECT name, MATCH (name) AGAINST ('vision black*' IN BOOLEAN MODE) as rel FROM ...
1
vote
2answers
40 views

MySQL Escape Characters

I'm getting an SQL error when trying to mix a match against in a SQL statement with a specific character which is not covered by MySQL escape functions in PHP. This is an example: The name to ...
0
votes
0answers
10 views

SQL Server 2008 R2 FREETEXTTABLE with FORMSOF(inflectional, string)

Can anyone confirm that using FORMSOF(inflectional, ...) is redundant when querying FREETEXTTABLE in SQL Server 2008 R2? Using FreeTextTable without formsof... gives me the same result list but with ...
2
votes
2answers
29 views

postgresql trigram search gets much slower as search string gets longer

In a postgresql-9.1 database, I have a table table1 with ~1.5M rows and a colum label that is trigram-indexed (actually, the trgm is on lower(unaccent(label)) (unaccent is made immutable for the index ...
1
vote
3answers
31 views

Speeding Up MySQL Full Text Search

I am doing a complex full text search on a table with 130k+ records. It takes .5 seconds to complete. I was hoping it would be a bit faster. Is there anything I can do to make it faster? Below is ...
2
votes
1answer
22 views

Problem with “not” in full text search

I don't understand why I'm getting these results with my full-text search query in SQL Server 2014. The following query returns results with "Supervisor" in the Title field and "Tokyo" in the ...
2
votes
0answers
26 views

FullText search using only some of columns in index

I have a full text index on (name,category,description) as shown below. mysql> show create table phppos_items; ...
1
vote
1answer
17 views

use fulltext formsof inflectional to generate terms

Is there a way to use the FullText query components to generate the list of words in the FullText dictionary that are the inflectional formsof a word? This query would give me matches based on the ...
0
votes
1answer
32 views

PostgreSQL full-text search retrival from id gap

I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part). There is a postgres function where I SELECT concat_ws(' ', ...
1
vote
0answers
15 views

How to full text search localized enums?

Suppose an SQL database like PostgreSQL with a simple table of products: id | name | type ----+-------------+------- 10 | MacBook | 1 11 | iPhone | 2 12 | Boeing 747 | 3 13 | ...
3
votes
1answer
66 views

Can jsonb do fast search by *value* or even a partial value? Other types?

I have a person table where arbitrary data must be inserted. Since I'm using Postgres 9.4, jsonb appears to be the right choice. Example data: id: 1, name: "Joe Doe", preferences: { color: "red" , ...
0
votes
1answer
20 views

Full text search on two tsvector columns

I'm trying to do a full text search across two columns of two separate tables in PostgreSQL 9.4.2. The tables and columns are: article.article_title keyword.keyword Where there is a many-to-many ...
3
votes
1answer
130 views

How can I return the full text of a document indexed in SQL Server Full-Text?

I'm using SQL Server 2014 with FileTables to store a large number of documents in different formats. The iFilters are working great, and everything is getting indexed with FTS + Semantic Search. Now ...
4
votes
1answer
54 views

Guidelines for full-text index maintenance

What guidelines should be considered for maintaining full-text indexes? Should I REBUILD or REORGANIZE the full-text catalog (see BOL)? What is a reasonable maintenance cadence? What heuristics ...
1
vote
1answer
54 views

Full text search on multiple joined tables

I'm trying to set up a full text search in PostgreSQL but coming across the problem that the more tables I join to the query the fewer the results I get back. The tables I am trying to join are: ...
2
votes
1answer
66 views

How to full text search worked with special character like “of”?

How to search with fulltext search if keyword has special character like "of", "-" ...etc select * from table1 join CONTAINSTABLE (table1,(Title,Description_HTML), '"of" and "Department"') tb2 on ...
1
vote
1answer
34 views

SQL Server full text search query takes way too long to execute

I've got query snippet as follows: SELECT * FROM CONTAINSTABLE(dbo.Analysis, *, 'FORMSOF(FREETEXT, "historic year-on-year exchange rates forecast year-on-year exchange rates")'); I'm running it on ...
2
votes
0answers
52 views

operator class “gin_trgm_ops” does not accept data type tsvector

ALTER TABLE users ADD COLUMN full_text_vector TSVECTOR; CREATE INDEX full_text_index ON users USING gin(full_text_vector gin_trgm_ops); The last line is giving me the following error: ...
0
votes
0answers
13 views

remove numbers from full text index

Is there any way to make SQL Server not full-text index numbers and currency values? According to sys.dm_fts_index_keywords the index has a very large number of unique values such as $ ...
2
votes
2answers
83 views

Fulltext search using multiple indexes

I have a table with several columns, say a, b, c, d that should be searchable. The problem arises when I need to search a, b, c separately from d (and vise-versa). AFAIK, there's no way to achieve ...
0
votes
0answers
44 views

SQL FullText Search and pdf

First of all, thank you for comming here : I wrote a script in SQL able to find words in a file, with a lot of help of some websites ...
0
votes
2answers
34 views

List full text indexes in Oracle database

Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index. How can I do something similar using Oracle? Is it possible?
0
votes
0answers
35 views

Mysql Full Text Search with word delimiter

I have the following paragraph, Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. <h1>Methodology</h1> Lorem ...
0
votes
1answer
63 views

MYSQL fulltext - What's the right way to get the relevance from one column?

I want to make one column more valuable than others like it's been done in query bellow.This example shows that only one fulltext index is needed And this answersuggests that three fulltext indexes ...
2
votes
1answer
76 views

Best index for similarity function

So I have this table with 6.2 millions records and I have to perform search queries with similarity for one for the column. The queries can be: SELECT "lca_test".* FROM "lca_test" WHERE ...
2
votes
1answer
29 views

How SQL Server distributes queries between Database Engine and FTS Engine

When a client submits a query to the database server, then SQL Server and its Database Engine parses, compiles, and execute those queries. But, if the client's application submits a query which ...
0
votes
0answers
74 views

How to find cause of SQL-Server Full-Text Index Auto-Population Error?

I have recently added a few full text indices to a database and AUTO populating is not working as I would have expected. Sample Table Definition CREATE TABLE [wrk].[WorkOrder] ( [id] ...
1
vote
1answer
176 views

mysql fulltext - exact match or nothing

I have mysql table with column tags, and would like to search exact match. For this data: proj7 invoice delivery proj7 exchange and this query MATCH(tags) AGAINST ('+proj7 +(invoice report)') ...
1
vote
0answers
37 views

Can a AWS PostgresSQL RDS instance be provided with a stop words file?

I need to provide a custom list of stop words. From the manual section on dictionaries the way to do this is to put a stopwords file in $SHAREDIR/tsearch_data/. Is it possible to do this when using ...
0
votes
1answer
40 views

Better way to full text query across multiple tables?

I've got a rather large query across multiple tables and their fulltext-indices which (unsurprisingly) is rather slow: SELECT t0.[Key], t0.[Nr] FROM dbo.[Customers_View] AS t0 WHERE (t0.[Deleted] ...
0
votes
1answer
48 views

Difference between “Table-Incremental” and “Table-Update” for “Population Type” when scheduling Full Text Index Population

I want to schedule a Full Text Index's population to keep it more up to date rather than leaving it to Automatic and not knowing the next population time as the users are complaining about the data ...
1
vote
1answer
41 views

Performing search on a column by query

Up until today I've used LIKE %term% to query for rows on particular column term. Then I've read about FULL TEXT index. And I think I misunderstood how Full text index works. I have an API that ...
1
vote
1answer
27 views

Oracle: How to get a list of CTXSYS.CONTEXT indices?

I can get a list of indices by querying USER_OBJECTS. How can I distinguish which of these indices are of type CXTSYS.CONTEXT?
2
votes
1answer
153 views

Levenshtein vs MATCH vs Others for best MySQL string match

I've a database with around 1.9 Million rows. My DB details Server: Localhost via UNIX socket Server type: Percona Server Server version: 5.5.42-37.1 - Percona Server (GPL), Release 37.1, ...
2
votes
0answers
52 views

Improving performance of a weighted search query in SQLite3

For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight: CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, ...
1
vote
0answers
37 views

Explain unexpected result in MATCH () AGAINST ()

I have a database with a fulltext column name. I made a query SELECT name, MATCH (name) AGAINST ('breaking bad') AS score FROM files WHERE MATCH (name) AGAINST ('breaking bad'); So that I can see ...
0
votes
0answers
39 views

Postgresql match all in full text search

Is there anyway that we can specify the tsquery to match all ? I have tried SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ '*'::tsquery; but it doesn't seem to work. It returns ...
1
vote
0answers
40 views

Can I Parse and/or include “And” in my SQL Statement for a full text index?

So I have a query that looks at a full text index like this: SELECT * FROM Table1 q INNER JOIN CONTAINSTABLE(Table1,TableField, @Keywords) search ON q.Key = search.[KEY] Now @Keywords ...
1
vote
1answer
388 views

MySQL FULLTEXT search not returning any results

I'm wondering why my fulltext search isn't returning any results. To me this seems pretty straightforward, but alas it's not working. I'm far from a DBA, but looking at the EXPLAINed query, I see ...
0
votes
1answer
17 views

SQL Server Full txt Search - Thesaurus Entries

I'm thinking of adding a full text search to our product (currently using SQL 2005 Enterprise), so just doing some preliminary research. I read that you can add thesaurus entries, so "Internet ...
1
vote
1answer
25 views

Search results mismatch after postgresql update

Today we had to update the PostgreSQL version on our database servers. Before update the version was 9.3.4 and the new one is 9.3.6. Despite the small difference in the database version and the ...
0
votes
1answer
29 views

Slow Full Text When using multiple full text searches

Thank you in advance for your help. I have been going bonkers. We recently made a change to our code by adding an additional contains to the below query. This has had a real negative impact on ...
0
votes
0answers
48 views

Making ordinal synonyms for Postgres full text search

I have a Postgres 9.3 database where users are mostly searching for locations by a combination of name and/or address (or fragments of). An example of an address would be "123 8th st". I've been ...
0
votes
1answer
192 views

SQL Server Full Text Search Engine is not returning consistent results

I'm working on an application that makes use of the Full Text Search Engine from SQL Server. In order to convert the user input into readable parameters for SQL, i'm using This very useful FTS query ...
0
votes
1answer
80 views

Oracle Text: query scoring with CTXCAT index?

If a text column is indexed with a CTXSYS.CONTEXT index it can return a score showing how well the text matches the query. create index ix on t(text) indextype is ctxsys.context parameters (''); ...
0
votes
3answers
80 views

What is the best way to have full text search where data in database is frequently updated

I am working on an application where database is continuously and periodically gets updated , the frequency of updating of data is daily, weekly, monthly, and yearly based on their type. I want to ...