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.
21
votes
5answers
2k 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
2k 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
2answers
358 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 ...
8
votes
3answers
681 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 ...
6
votes
2answers
1k 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
106 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
345 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
1k 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
67 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 ...
5
votes
1answer
706 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
222 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
3k 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
1answer
789 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 ...
4
votes
2answers
267 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
630 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
236 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` ...
4
votes
1answer
211 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
2k 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 ...
4
votes
1answer
253 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)
...
3
votes
2answers
166 views
Full Text Search With PostgreSQL
i have a table with this rows:
Stickers
------------------------------------------------------
ID | Title |Keywords (ts_vector)
------------------------------------------------------
...
3
votes
2answers
4k 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 ...
3
votes
4answers
3k 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 ...
3
votes
2answers
300 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 ...
3
votes
1answer
310 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
2answers
54 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 ...
3
votes
1answer
585 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
1answer
2k 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
2answers
211 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
523 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
101 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
1answer
712 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 ...
3
votes
1answer
250 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 ...
3
votes
2answers
368 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
321 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 ...
3
votes
0answers
21 views
custom population of full text index
Is there any way to populate a full text search index without storing the backing information in the database?
I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
3
votes
1answer
26 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 ...
2
votes
2answers
181 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.
2
votes
2answers
264 views
MySQL Full Text index issue
I Have a table articles with following data
mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title | body ...
2
votes
1answer
534 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
2answers
59 views
MySQL - SQL statement to test minimum length of the word 'ft_min_word_len'
I'm trying to understand FULLTEXT indexes.
Is there some SQL query to test 'ft_min_word_len' variable?
I've tried to match four characters only in the example below but it doesn't work. Why the ...
2
votes
1answer
341 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.
...
2
votes
1answer
91 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 ...
2
votes
3answers
335 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:
...
2
votes
1answer
620 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
253 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 ...
2
votes
1answer
219 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, ...
2
votes
1answer
63 views
Reorganize full text catalog is offline or online?
I need to schedule a Full Text Search Maintenance and I was reading this link: http://msdn.microsoft.com/en-us/library/ms176095.aspx. Reading this documentation I am supposing that I need to ...
2
votes
1answer
55 views
Are there differences in performance of getting data between FullText Search and indexed columns
In SQL Server 2008 R2 I have a query which looks like
create stored procedure give_me_art @filter varchar(15),@skl_id int
begin
select
id,naziv,sifra,isnull(lager.kolicina,0) as lager
from art
...
2
votes
2answers
72 views
Looking for a database that supports fulltext but does not lock table on insert or update
I have a program that needs to insert and update a table very frequently (200+ insert or update queries per minute), and this table should also support fulltext search.
I am currently using MySQL ...
2
votes
1answer
111 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, ...
