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

2
votes
1answer
117 views

why/how does the number of matched columns influences the way of excecuting a query

Imagine the following situation: Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index. FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4 Table B uses InnoDB and ...
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 ...
4
votes
2answers
876 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 ...
0
votes
1answer
56 views

2 Very similar queries return different number of result (one is null)

I want to find businesses near a point that contain a word that start with sea lat=-6.185068&long=106.773121&keyword=sea&distance=5000&startfrom=0&howmany=20&prominent=5 ...
-2
votes
1answer
2k views

Why does MATCH (FullTextSearch) AGAINST ('*' IN BOOLEAN MODE) return nothing?

MATCH (FullTextSearch) AGAINST ('k*' IN BOOLEAN MODE) return something Namely anything with a word that start with k MATCH (FullTextSearch) AGAINST ('ku*' IN BOOLEAN MODE) return less MATCH ...
0
votes
1answer
225 views

Why number of matches in fulltext search get less when we put less strings?

SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('resta*' IN NATURAL LANGUAGE MODE) show 5 results SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('restaurant*' ...
1
vote
1answer
427 views

which database engine is best for full text search on a 10M rows table?

I'm creating a search engine and needs to search keywords in a text field (about 300 words in each text field, unstructured). I'm thinking about noSQL\MySQL+Sphinx, the performance impact here is ...
1
vote
1answer
913 views

Full Text Search not searching special character

I am working on SQL Server 2008. I have created full text index on a Table(Column) and I am trying to find. SELECT * FROM TblName WHERE CONTAINS(ColName,'@#'); This is giving no results. But I ...
0
votes
1answer
86 views

Is there a way to display current noise words in SQL 2000 Full Text Indexing?

I know this is ancient technology, but sometimes upgrading is not always an option. Does anyone now how to list the actual noise words that are currently in use on a SQL 2000 Full Text Index? We ...
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, ...
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 ...
5
votes
1answer
639 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` ...
1
vote
1answer
91 views

What is the use of creating full text catalog in full text indexing

Just want to know about the processes happening in full text indexing. I know by defining the full text indexing for a table, it will index all the words saved in that table. But i didn't get the use ...
0
votes
0answers
393 views

What's is WITH QUERY EXPANSION MODE in MySQL Fulltext Search

MATCH (col1,col2,...) AGAINST (expr [search_modifier]) search_modifier: { IN NATURAL LANGUAGE MODE | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION | IN BOOLEAN MODE | WITH QUERY ...
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 ...
2
votes
1answer
130 views

General strategy for updating a full text search index

I am using a SQL Server 2008 R2 DB with FTS enabled on it. I applied an FTS index on a table. The table is frequently updated (I mean the CUD operations). Keeping this in mind, I came up with a ...
2
votes
1answer
1k views

Best way to compare strings using pre-built ngrams?

I'm struggling with an n-gram problem and have been hunting the Web for examples to help me out and have noticed n-grams being discussed on this forum. I'm working on a system where we've got scraped ...
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) ...
2
votes
1answer
183 views

Building a custom database in C with/without SQL? [closed]

Provide link or tutorials to build a custom database management in C. I am looking for Full Text Search options of about 10GB DB.
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 ...
1
vote
0answers
217 views

Full Text Search with Encryption SQL Vs NoSQL? [closed]

Can any tell Which will be better for online and offline projects
2
votes
3answers
611 views

Which database is best for deeply embedded database or through C DLL?

I want a deeply embedded database. Deeply embedded means that the server is started by an application and closed by the application with no tcp/ip or 0 port. The main features of consideration are: ...
4
votes
2answers
621 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 ...
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
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 ...
1
vote
1answer
1k views

SQL Server 2008 R2 Most Efficient Full Text Search Field Type

In SQL Server 2008 R2, what is the most efficient column type to store data enabling full text search? I can currently see two options, nvarchar and varbinary. Which one performs better and why? ...
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 ...
1
vote
1answer
1k 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 ...
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 ...
2
votes
2answers
480 views

MySQL Full Text index issue

I Have a table articles with following data mysql> select * from articles; +----+-----------------------+------------------------------------------+ | id | title | body ...
1
vote
1answer
270 views

MySQL full text search ranking

I found in this article the formula that MySQL use to ranking in FTS w = (log(dtf)+1)/sumdtf * U/(1+0.0115*U) * log((N-nf)/nf) Where dtf is the number of times the term appears in the ...
1
vote
2answers
1k views

SQL Server Fulltext search against big amount of search terms over some period of time

I've got a task where I must do a fulltext search in SQL Server 2008 against big amount of search terms (30-60 search terms) combined with OR. Some search terms may have partial match (asterisks at ...
2
votes
1answer
948 views

SQL Server 2008: What are the English Word Breakers?

I'm trying to replicate what SQL Server 2008 is doing when generating full-text indexes, but I'm having a hard time finding a list of which characters SQL Server 2008 considers to be word breakers. ...
2
votes
1answer
638 views

Can I get similar Full Text Search functionality without using Full-Text Search?

Normally, I wouldn't post this kind of question here, but I can't seem to find anyone who has actually used SQL Server Full Text Search, so I'm posting here. I'm creating a solution for a client that ...
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 ...
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 ...
3
votes
1answer
877 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
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?
2
votes
2answers
1k views

Is it better to use FREETEXT or CONTAINS Full Text Searches when searching by single characters

I am trying to come up with the most efficient way to search for records in a collection of about 130,000 total records. The long, short of it is we have about 130,000 Active Directory records. I am ...
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 ...
2
votes
2answers
370 views

What does “GIN doesn't support full index scans” mean?

I'm a RDBMS newbie and I have a PostgreSQL database of more than 50 millions lines, growing everyday. There is a column 'text', which contains human-generated text of around 20 words. I need to be ...
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 ...
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 ...
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 ...
2
votes
2answers
905 views

SQL 2008, how does it link Full Text Catalog to a filegroup

While creating Full Text Catalog, if "on filegroup" is specified - it creates the catalog on this file, but on MSDN I read "On Filegroup" clause has no affect?
2
votes
2answers
374 views

Should SQL full text search match the value test2 when searching for test?

if i have table data that is indexed using full text search in Microsoft SQL server 2005 that includes raw table data such as "test2" should a query using FTS find a match if the search string is ...
2
votes
1answer
243 views

Why doesn't FTS match any records?

I created an indexed view with a few fields that i created a full text search based on these fields. here is my view: alter VIEW vFullTextSearch WITH SCHEMABINDING AS SELECT M.Id as MessageId, ...
1
vote
2answers
236 views

create a FTS in sql2005 on multile fields in multiple tables?

How can I create a Full Text Search catalog on 3 fields where one is in a second table?