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
297 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 (`...
4
votes
2answers
162 views

Integrate MySql Full Text searches in CMS

I'm developing my own CMS and using MySql v5.5 DB to save the content items records. Due to a technical limitations, I can't upgrade my database to a newer version that support Full-Text searches on ...
2
votes
1answer
90 views

Optimized MySQL “optional” FULLTEXT search on multiple tables

I need to select data with optional columns in where clause based on client input. my query is like this: SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt FROM a LEFT JOIN b ON a.b_id=b.id ...
0
votes
0answers
42 views

Where to put snowball files to add a snowball stemmer to PostgreSQL database?

I've downloaded Czech snowball stemmer source files (.c, .h files) and compiled them into .o files and I know the correct SQL syntax for adding the dictionary (it's in the docs). I keep getting the ...
3
votes
2answers
190 views

How to match all words of one column against words in another column

I have two tables. Table A: Company_name ------------------------------- Tata consultancy Infosys tech Atm service Air force firm Table B: Company1_name | Id ----------------...
5
votes
2answers
709 views

MySQL fulltext selection performance

I have 1.5M rows in a table. Following is the table create code: CREATE TABLE `jobs` ( `id` INT(8) NOT NULL AUTO_INCREMENT, `job_id` VARCHAR(50) NOT NULL DEFAULT '', `title` VARCHAR(255) ...
1
vote
1answer
56 views

Match all in a full text search

Using PostgreSQL 9.4, 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 ...
1
vote
0answers
44 views

Restoring SQL Server 2012 Enterprise to Web Edition not possible due to full text partitioning

I have an SQL Server 2012 Enterprise database I’m trying to restore to SQL Server 2012 Web Edition instance. I’ve used sys.dm_db_persisted_sku_features to find features to remove before a backup/...
0
votes
0answers
78 views

Mysql FULLTEXT index not used in Many to Many table join search

I've got a set of data in the Books table, with BookID as the primary key. I've also got Subject and Location tables, each of which is joined to the primary Books table with a Many to Many table. ...
4
votes
2answers
1k views

ts_rank massively slows my query, how can I improve performance?

Here's the table definition Table "public.kb_article_contents" Column | Type | Modifiers ------------+----------+----------- article_id | smallint | not null contents | text | not ...
0
votes
1answer
109 views

mysql innodb db full text search FTS_DOC_ID column

I am adding full text search to already existing mysql tables that have primary auto incrementing key of id integer. When I add the full text index I get a warning: /*!50604 ALTER TABLE `...
17
votes
3answers
4k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...
0
votes
0answers
221 views

Full text search - Search with contains plus wildcard (*) not working properly

Although I know sql, I am a web developer. So this is a pretty new to me. I am working on a bug in a webapp regarding Full text Search, and some inconsistent results with it. You can see what I've ...
0
votes
1answer
38 views

Why my index don't update?

I have created this index ORACLE/VIDEO# create index titolo_index 2 on titolo(titolo) 3 indextype is ctxsys.context 4 ONLINE 5 ; I do a research select * from generale where CONTAINS(...
2
votes
0answers
86 views

Postgresql: Limits of tsquery field

I'm using a tsquery field in order to pre-compute batch searches, but I'm encountering this error while updating a tsquery field on a table: ERROR: row is too big: size 10704, maximum size 8160 ...
3
votes
1answer
307 views

Unsupported languages in Postgres Full text search

My web application supports 3 languages, English, Traditional Chinese, Vietnamese. I understand that Traditional Chinese and Vietnamese is not supported in Postgres' Full Text Search. If I were to go ...
1
vote
1answer
70 views

Looking for simple contains method when searching text in postgresql

Im new to the PostgreSQL world so I do apologize in advance for not knowing if there is an obvious answer to these questions. Basically I'm looking for two things. First a simple "contains" based ...
3
votes
1answer
191 views

Faster query with pattern-matching on multiple text fields

I have one Postgres table with more than 20M tuples: first_name | last_name | email ------------------------------------------- bat | man | batman@wayne.com arya | vidal | foo@...
1
vote
1answer
291 views

How to set weights on a Postgres TSVECTOR column?

Currently, I'm using Postgres 9.3.5 for full-text search but I don't want to set weight or convert to tsvector on the fly (a lot of articles suggest that, and that just seems like a terrible idea to ...
3
votes
1answer
84 views

Why is my tsv index not being used?

I'm trying to get the postgres full-text-search facility functional. I have two tables, one I created just for testing, and the actual one I want to be able to search: Test Table: webarchive=# \d ...
1
vote
1answer
2k views

At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?

I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run. I guess that it ...
0
votes
0answers
98 views

SQL-Server full-text search does not use thesaurus in CONTAINSTABLE unless the FORMSOF is used

I am trying to improve the full-text search capabilities of system. I have created few synonyms in SQL Server Thesaurus according to MSDN article. <expansion> <sub>running</sub> ...
15
votes
1answer
4k views

There is insufficient system memory in resource pool 'internal' to run this query

One of our production servers is reporting an error in the log Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool 'internal' to run this query. I ...
5
votes
1answer
458 views

Slow fulltext search for terms with high occurence

I have a table that contains data that is extracted from text documents. The data is stored in a column called "CONTENT" for which I have created this index using GIN: CREATE INDEX "File_contentIndex"...
8
votes
2answers
275 views

Trigram search gets much slower as search string gets longer

In a Postgres 9.1 database, I have a table table1 with ~1.5M rows and a column label (simplified names for the sake of this question). There is a functional trigram-index on lower(unaccent(label)) (...
2
votes
1answer
475 views

How to repair all indexes in a MySQL database?

My database got corrupted, and now the php app is throwing this error: Invalid query: Can't find FULLTEXT index matching the column list The error is not very useful, since it doesn't say what ...
3
votes
0answers
104 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 | ...
0
votes
0answers
182 views

Full text search of values with a special character (') produces unexpected results

I'm having a problem with MySQL full text search. I have a users table that has a single column, username. The problem appears when I'm trying to search for a user with a special character (') in its ...
1
vote
0answers
83 views

Postgres search and replace

What is the difference between the search in search and replace, (the replace function) and full text search (tsvector) in Postgres? Or, put another way, can tsvector be used in search and replace (...
3
votes
1answer
893 views

How do I get more details on a full text catalog error?

I have an error that is repeatedly generated on a SQL Server 2005 instance running the databases for MS Team Foundation Server. 'Severity 16' occurred on INSTANCENAME Looking in the SQL Server ...
4
votes
2answers
307 views

GIN index not used when adding order clause

I'm trying to speed up a query that executes three ILIKE queries and reduces these with or (returning the overall count and 10 entries) SELECT *, count(*) OVER() as filtered_count FROM "users" ...
0
votes
1answer
196 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` bigint(20)...
4
votes
1answer
536 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" , ...
2
votes
0answers
470 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 ...
0
votes
0answers
50 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 ...
1
vote
1answer
126 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 ...
1
vote
3answers
54 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
33 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
32 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; +--------------+----------------------------------------------------------------------...
3
votes
1answer
254 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 ...
1
vote
1answer
54 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(' ', a."...
1
vote
1answer
85 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 ...
1
vote
1answer
325 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: ...
3
votes
1answer
290 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 ...
8
votes
3answers
2k views

Full text search results in a large amount of time spent in 'FULLTEXT initialization'

I'm currently trying to run some queries against a data dump of Stack Overflow's comments. Here's what the schema looks like: CREATE TABLE `socomments` ( `Id` int(11) NOT NULL, `PostId` int(11) ...
2
votes
0answers
249 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: SQLSTATE[...
2
votes
2answers
383 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 ...
1
vote
0answers
35 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 $ 123,...
2
votes
2answers
765 views

How do I rebuild single full text catalog (or why is “rebuild” full text catalog option disabled)

In SSMS. I have Full Text Catalog defined. If I select "Full Text Catalogs" folder - there is an option "Rebuild All" and it is enabled. But if I select individual FTC - "Rebuild" option (in context ...
1
vote
1answer
458 views

mysql fulltext query is very slow

I have one table for User detail in MySql with about 500000 records in it. I have also created fulltext index on firstname, lastname field on this table. but when I am trying to search any single ...