F all of you.

Corpus Cristi -or- Database Searching

So there's this table with about a million records, and a dozen nvarchar or ntext fields. A user wants the ability to enter any text snippet and have any record with that value anywhere in any part of the values of any of those columns as a result. This text snippet isn't necessarily a whole word, nor is it necessarily the beginning of the word (e.g. they want 05025 to pull up the record where the guy mentions record BG20050910052576), which rules out Full-Text Search being useful (in SQL Server). One is relegated to doing terrible, full table scanning LIKE comparisons with every column of every record. Terrible.

In that scenario, does anyone know of any technological solutions? Any indexers of partial word fragments that one should look at in this scenarios? I'm going to look at the Lucerne engine, but my gut instinct and pedestrian knowledge of full-text indexing tells me that there's going to be little consolation.

Oh, and no I didn't design this.
Permalink Dennis Forbes 
August 30th, 2005
Lucene would have been my suggestion. It think it does support pattern searches, not just keyword, so you should be ok.
Permalink son of parnas 
August 30th, 2005
Lotus Notes full text search has wildcard operators, and it used the Verity search engine. http://www.verity.com/
Permalink ronk! 
August 30th, 2005
Add a text field, call it "fulltext" or something.
Add a trigger on the record, set to trigger on inserts or updates. In the trigger concatenate the fields into the fulltext field.

Run your queries on the fulltext field.

Philo
Permalink Philo 
August 30th, 2005
I'm not sure fulltext indexes would cover this case:

"they want 05025 to pull up the record where the guy mentions record BG20050910052576"

Do they?
Permalink Almost H. Anonymous 
August 30th, 2005
RTFP, you idiot, Philo.
Permalink Anon 
August 30th, 2005
Wow. Talk about thick-headed.

You last two, I mean.

I'm sorry I arbitrarily picked the term "fulltext" - call it "ConcatText"

This has nothing to do with full text searching - I'm talking about creating a field which has all the fields of interest that you can then run the query (even if it has to be a LIKE '%' + SEARCHTERM query) against.

Philo
Permalink Philo 
August 30th, 2005
>> So there's this table with about a million records

Again, RTFP, you idiot, Philo.
Permalink Anon 
August 30th, 2005
>>>> So there's this table with about a million records
>>Again, RTFP, you idiot, Philo.

So what? 1 million records is not a lot for an indexed table.
Permalink Masiosare 
August 30th, 2005
Like %Searchterm doesn't use indexes.

But then, I'm noting that "Anon" isn't coming up with anything better.

I have to wonder if he sits in team meetings and every time someone suggests a solution he says "No, that's stupid and won't work, you fucking moron"

Philo
Permalink Philo 
August 30th, 2005
Dennis, you in a position to give it a try and let us know what the response time is like?

Philo
Permalink Philo 
August 30th, 2005
>> I have to wonder if he sits in team meetings and every time someone suggests a solution he says "No, that's stupid and won't work, you fucking moron"

Hey you must be boss, 'cause that's exactly what i do! ...you fucking moron!!
Permalink Anon 
August 30th, 2005
>> Dennis, you in a position to give it a try and let us know what the response time is like?

No, that's a stupid idea and won't work, you FUCKING MORON!!
Permalink Anon 
August 30th, 2005
Oops, you found an error!