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
3
votes
2answers
4k views

Oracle “matches” multiple columns

I am developing a web service where a term of search is given, then I return all the matches from employees table. In MySQL I used to do the following: SELECT * FROM employees WHERE MATCH(name, ...
0
votes
1answer
266 views

MYSql Full Text Boolean search and relevancy score improvements

I am using a mysql BOOLEAN full text search as shown in the query below and am getting what I think to be bad results that could be more relevant. Query: SELECT phppos_items.name,MATCH (phppos_items....
0
votes
1answer
37 views

How to use FULLTEXT in mysql innoDB?

Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance. I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference ...
0
votes
1answer
239 views

SQL Server FTS get tokens from string

What is the query to get the tokens in a string from SQL Server 2008 R2? I need it to highlight results of FREETEXT
1
vote
0answers
36 views

Synonims in SQL Server 2008 R2 FTS

The Search feature on my website is based on SQL Server's Full Text Search (I insert everything relevant to a single table and this table is in the FTS Catalog). We don't have money to buy a ...
2
votes
0answers
210 views

Why does score for mysql boolean match give different score?

I am attempting to use MySQL's fulltext search using Boolean match on multiple columns, with multiple search phrases. I cannot understand the score returned though. The score returned is typically an ...
0
votes
1answer
43 views

FullText Search

We came across an issue using FullText Indexes and SQL 2008 R2, not sure if it's by design or I am missing something. When we search using a fulltext index, I noticed SQL appears to trim leading and ...
1
vote
0answers
97 views

full-text search 7608 (0x80004005) error message debug

Someone tries to run the following line : exec sp_fulltext_column 'tbElementFullTextMvCustomProperty5', 'EMVCP_E_NAME', 'add', 0, NULL and then receives the following error : (1 row(s) affected) (...
3
votes
1answer
2k views

SQL Server: no search results caused by noise words

I've moved a database from SQL Server 2000 to SQL Server 2014 via an import/export through SQL Server 2008 to make it compatible to be imported in 2014. So far that has worked fine. I found out that ...
1
vote
2answers
8k views

Filtering special characters in to_tsquery

This question has been asked before here and here. I want to provide full-text support using postgreSQL via to_tsquery. However, since escaping characters leads to complications, I think it is okay to ...
0
votes
1answer
1k views

postgresql custom TEXT SEARCH CONFIGURATION with tsvector_update_trigger

I would like to use a custom text search configuration with tsvector_update_trigger to be able to update tsv field with french dictionary and unaccent. So I did that : CREATE TEXT SEARCH ...
0
votes
1answer
129 views

MySQL indexing issue when trying to search for a part of a string/word

What we're running into: Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We ...
3
votes
1answer
818 views

Full text search in XML documents

I would like to do full-text search in XML documents. However, I can't get matches for attribute values (I get a syntax error if I provide the full attribute value including the : and / characters in ...
1
vote
1answer
451 views

Full text search error: Invalid locale ID was specified

Query involving full text search is generating the following error message after restoring the database to a new server: Invalid locale ID was specified. Please verify that the locale ID is ...
0
votes
1answer
455 views

Full text indexes on big tables

I have a news website where I store my articles in a table that looks like that: Id title data Where data contains the content of the article and is the biggest column in the database by far. ...
0
votes
1answer
762 views

A plain text based database [closed]

The list of the files on my hard disks has grown up to a million, the more or less, of records, each consisting of a eigth or ten fields, es. support id - file size - full path name - md5 hash - ...
5
votes
1answer
3k views

Return top 100 words from strings

Not sure this is even possible but I was wondering if there is any way of using PostgreSQL to return the top 100 most occurring words from a database column containing strings of varying lengths? The ...
1
vote
2answers
178 views

Fulltext searching on shared hosting - 2000 Entires with around 500 words each

I'm fairly new to db administration and would appreciate some help. I want to host a site which requires full text search on around 2000 items, each with around 500 words each. I need this to be ...
1
vote
1answer
874 views

Database Design and structure

I want to make colleges search system where a user enter any keywords in a search box and we can provide them some institutes with their details like institutes names, belonging hotspots, cities, and ...
5
votes
2answers
509 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 ...
5
votes
1answer
907 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 ...
2
votes
1answer
1k views

How do I use freetexttable against multiple tables?

I have a database which (amongst other things) contains tables for support tickets. Each ticket has a short summary, as well as a description. Each ticket can have multiple notes, each of which has an ...
12
votes
2answers
5k views

Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it ...
2
votes
0answers
150 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/...
4
votes
2answers
1k 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 -----------------...
3
votes
1answer
2k 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 `...
0
votes
1answer
62 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
285 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 The ...
5
votes
1answer
19k 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 ...
7
votes
1answer
8k 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@...
6
votes
1answer
13k views

Change clustered index without dropping the primary key

In the past, I have chosen the datetime column created as inappropriate clustered index in the table. Now I concluded (base on the execution plan) that it would be better to choose the ID identity ...
3
votes
1answer
450 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 ...
3
votes
3answers
6k views

Search across all columns in a table

As the title says, I want to create a query that searches a keyword across all columns in that table. So I want to simplify this query: SELECT * FROM tblCustomers WHERE (col1 LIKE '%keyword%' OR ...
0
votes
2answers
1k views

Slow mysql query full text search tag database

In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. ...
1
vote
0answers
2k 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
221 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 (...
6
votes
2answers
2k 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" ...
8
votes
2answers
4k 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"...
4
votes
1answer
1k views

FullTextSearch indexing/searching words with special characters

I'm trying to query FullTextSearch indexed column for hashtags SELECT Bio FROM Users WHERE CONTAINS (Bio,'promoter') SELECT Bio FROM Users WHERE CONTAINS (Bio,'#promoter') both results returns the ...
0
votes
1answer
716 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)...
3
votes
0answers
3k 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 ...
6
votes
0answers
3k 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 ...
10
votes
2answers
2k 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 ...
1
vote
1answer
7k 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 (`...
2
votes
2answers
2k 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 ...
17
votes
2answers
2k 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)) (...
1
vote
3answers
175 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
258 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
57 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; +--------------+----------------------------------------------------------------------...
5
votes
1answer
3k 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
3 4
5
6 7
10