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

25
votes
5answers
5k views

What database technologies do big search engines use?

Does anybody knows how Google or Yahoo perform searches for keywords against very very huge amounts of data? What sort of database or technologies do they employ for this? It takes few milliseconds, ...
10
votes
5answers
3k views

SQL Server 2008 Full Text Index Never Seems to Complete

Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search. Each time a new record is added or updated in one of the indexed tables, the indexing ...
9
votes
3answers
2k 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 ...
9
votes
2answers
464 views

SQL Server Filetable document properties

I'm using SQL Server 2012's Filetable to store documents and search them with Semantic Search. I was wondering if there is a way to list all the document properties (meta data). There is a way to ...
6
votes
1answer
2k views

Where are Full-Text indexes in SSMS 2008 R2

I created a new database, a few tables, and full-text index & catalog using SQL Server Management Studio without any problem. I wanted to copy the T-SQL creation scripts for each of those to ...
6
votes
1answer
224 views

BM25 (full-text search) implementation in SQL Server

I've hit a stump, while trying to implement the BM25 algorithm in SQL Server 2008 R2. I know that SQL Server includes the Full-Text Search option, which already implements a variant of BM25, but I ...
6
votes
2answers
2k views

Why does MyISAM support FULLTEXT Searching and InnoDB does not?

I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
6
votes
1answer
125 views

Can I get the union of the a Postgres fulltext index?

I have a fulltext index on a table. Is it possible to retrieve the set of terms used in the index (gist or gin)? With weights if possible? To clarify: If I have the following table: create table ...
5
votes
1answer
75 views

Full Text Search Term Casing Affects Results

I have been experiencing a full text problem when searching using CONTAINS. I have the full text catalog accent sensitive setting set to false. The table contains following rows: Microsoft C# ...
5
votes
1answer
821 views

Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
5
votes
1answer
2k views

How to install SQL Server Express 2005 with Full text search

I am in need to Install SQL server 2005 Express edition with Full Text Search capability. I don't know what to download and the order of installing the Database management system. Could someone ...
5
votes
1answer
465 views

Full-text index in partitioned table

I have a table with just over a million records with a full-text index. This table has been split in two in the past, and the data older than a certain date is moved to another table with the exact ...
5
votes
1answer
509 views

Full text search through many tables in MySQL

We have high traffic NEWS websites, I want to add a feature that every user can search through over all content of site, such as news, polls, comments, galleries,etc . Each of contents type has its ...
5
votes
1answer
643 views

MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

I have a basic table : create table fullTextTest ( id INT(11) NOT NULL, superText CHAR(255) NOT NULL, superLongText TEXT NOT NULL, primary key (`id`), FULLTEXT KEY `superText` ...
5
votes
2answers
2k views

Postgres full text search with multiple columns, why concat in index and not at runtime?

I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns. The postgres docs talk about creating a ts_vector ...
5
votes
1answer
7k views

Why is LIKE more than 4x faster than MATCH…AGAINST on a FULLTEXT index in MySQL?

I'm not getting this. I've got a table with these indexes PRIMARY post_id INDEX topic_id FULLTEXT post_text Table has (only) 346 000 rows. I am trying to perform 2 queries. SELECT ...
5
votes
1answer
976 views

SQL Server 2008 Containstable generate negative rank with weighted_term

I have a table with full text search enabled on Title column. I try to make a weighted search with a containstable but i get an Arithmetic overflow for the Rank value. The query is as follow SELECT ...
5
votes
2answers
343 views

SQL Server Full Text Search - .rtf files incorrectly indexed by rtf tags

I've set up a full-text index on my SQL Server DB on a varbinary(max) column. I have a type column specified, which contains the extension of the file, such a ".doc", ".pdf" etc. However, I have ...
5
votes
2answers
261 views

Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search

Followings are what I need to do on my project: 1- If a search term is new and unique, then add the search term 2- Show lastest searched 1000 search terms (I don't need to store dates of all search ...
4
votes
3answers
549 views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
4
votes
2answers
10k views

Why does simple ALTER TABLE command take so long on table with full-text index?

I have a large (~67 million rows) name-value table that has full-text indexing on the value column (DataValue). My question is: If I try to run the following sql command: ALTER TABLE VisitorData ...
4
votes
4answers
5k views

Property PopulationStatus is not available for FullTextCatalog… How to fix?

I've created a new catalog (case insensitive, make default catalog), when I try to right click > properties to add tables I get this error: Property PopulationStatus is not available for ...
4
votes
1answer
2k views

Using Solr/Lucene for searching non-text tables?

I am creating a web application to retrieve subsets of one large (4m rows) table. The 4m rows only change once a year. The table has 200+ columns of types boolean and numeric. It has no text columns. ...
4
votes
2answers
3k views

How to search for a specific column name in all the tables in MySQL Workbench?

In MySQL Workbench, is it possible to search for a specific column name in all the tables? (Writing the string to look for in the field at the top right does nothing). Thank you.
4
votes
2answers
877 views

Slow ORDER BY with LIMIT

I have this query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2("city")) @@ to_tsquery('simple',unaccent2('wroclaw')) order by displaycount I'm happy with it: "Sort ...
4
votes
2answers
5k views

How to set full text catalog location SQL Server 2008/2012

I want to upgrade my SQL Server 2005 database to SQL Server 2012. In SQL Server 2005 you can set the location of the full text catalog when you create it. But in SQL Server 2012 (and in SQL Server ...
4
votes
2answers
907 views

maintaining full text index on large table

I'm on SQL Server 2008 and have a table, for reporting purposes, with 500,000 records that will easily reach the millions. The table will employ a full text index for rapid searching on a handful of ...
4
votes
2answers
622 views

How to sync a database with an external full text engine?

How do you keep your external full-text engine (Lucene, Solr, etc) in sync with your database data? For example, keeping track of inserts, deletes, updates. My current solution involves a big union ...
4
votes
1answer
1k views

Alternatives to LIKE and FTS for mid name search

I have a question about what are the alternatives for LIKE '%%' and Full-Text Search when it comes to searching organisation names, firstnames, surnames, etc. The LIKE '%%' causes table scan, however ...
4
votes
1answer
343 views

How much data can SQL Server full text indexing handle?

I realize that the question is vague and it depends on hardware and our needs. We currently have 5 million rows of data, a total of 5GB of data which we want to index using full text indexing. Our ...
4
votes
1answer
376 views

Can Oracle Text query return the terms found?

I am querying an Oracle 10g database. The database contains a table with documents that has an oracle text index. I query the database using a query like SELECT SCORE(1), title from news WHERE ...
4
votes
1answer
57 views

Combining full-text and scalar index

Let's say we have a database of 12 million names and addresses that need to be searchable using full-text, but each row contains an integer value too, let's say COMPANYID. The table contains around ...
4
votes
2answers
201 views

Fulltext search limiting users possibilities

We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure. CREATE PROCEDURE [dbo].[SearchPages] ...
4
votes
1answer
331 views

Sql Server Full Text Search one table for text values in another table

Is it possible to run a full text search on an FTS-indexed column for all values in another table? Conceptually it'd look like select d.* from Docs d, Tags t where CONTAINS(d.fulltext, t.tagvalue) ...
4
votes
1answer
277 views

Full-text search with german umlauts

I created a table using German (Germany) as the Full-text language, in the settings I tried setting accent sensitivity to both sensitive and insensitive. I, however, get following odd results. The ...
4
votes
1answer
159 views

Fulltext stoplist replication

In MS SQL Server 2008 R2 there is replication of table with fulltext index on it. But stoplist that is associated with replicated fulltext index doesn't replicate. Is there any possibility to ...
3
votes
2answers
2k views

Installing Full Text Search to SQL Server 2008 R2

I installed sql server 2008 R2 Express successfully, then realised I need to get Full Text Search. So I downloaded the "Advanced Services Installation Package", but when I run it there is no option in ...
3
votes
2answers
372 views

What characters are word breakers in English for SQL Server 2005 and 2008 R2?

I can find what DLL supports English word breakers by using sp_help_fulltext_system_components but I have not been able to find an actual list of the word breaking characters for English (like blank, ...
3
votes
1answer
385 views

How to get the Oracle Text Index's underlying Virtual XML Document?

The Oracle Text Reference 10g states that when we index our table using multi-column datastore, the system concatenates the text columns, tagging the column text, and indexes the text as a virtual XML ...
3
votes
1answer
3k views

Why full-text-search returns less rows than LIKE

I don't get full-text-search working as I want it to, and I don't understand the differences in the resultlists. Example statements: SELECT `meldungstext` FROM `artikel` WHERE `meldungstext` LIKE ...
3
votes
3answers
2k views

slow query with fulltext and left join

I have 3 tables about 250,000 records. table1 is MyISAM, add fullindex with title and content, add index with pid. table2 and table3 are InnoDB, add index with pid. Only query table3, just cost ...
3
votes
1answer
3k views

Mysql fulltext search my.cnf optimization

I have open a question on http://serverfault.com/questions/353888/mysql-full-text-search-cause-high-usage-cpu Some user recommended asking here. We built a news site. Every day we will input tens of ...
3
votes
1answer
1k views

Are there disadvantages to using Full-Text Search in SQL Server?

Are there disadvantages to using Full-Text Search in SQL Server? Is there much of an increased CPU or memory load on the server?
3
votes
1answer
878 views

FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional

fulltext match is ignoring its index when I add a number of words conditional for its boolean mode. The selects are as follows: explain select * from seeds WHERE MATCH(text) AGAINST ("mount ...
3
votes
1answer
33 views

is it possible to do a reverse pattern search in a database?

I have 2 database tables with rows containing strings. If I combine two strings, one from each table, as input I need to be able to find where the parts of the strings are located in each table. For ...
3
votes
2answers
563 views

MySQL Full Text search increase relevance for exact matches?

I have a MySQL database of ~10,000 organisation names that I want to be able to search. I would like to use a full text search because this would enable me to find "institute of doobry" by searching ...
3
votes
2answers
523 views

SQL Server 2005: Full-text search space requirements

Is there a way to calculate the physical drive space required for a full-text search catalog? Space is cheap, but I'd like to know what I'm getting into. I'm looking at a table of about 200 articles ...
3
votes
1answer
190 views

Can underscore be forced as a word splitter without a full-text parser plugin?

Is there any way in MySQL to force underscore to become a word splitter character (like space or dash) without having to use a fulltext parser plugin? If not, does such a plugin already exist or do I ...
3
votes
3answers
591 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 ...
3
votes
1answer
375 views

Mysql fulltext boolean ignore phrase

I'm trying to come up with a mysql boolean search that matches "monkey" but ignores / doesn't take into account any matches of "monkey business". Something like the following is the general idea but ...