Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Why don't any of the major RDBMS systems like MySQL, SQL Server, Oracle, etc. have good full text indexing support?

I realize that most databases support full text indexes to some degree, but that they are usually slower, and with a smaller feature set. It seems that every time you want a really good full text index, you have to go outside the database and use something like Lucene/Solr or Sphinx.

Why isn't the technology in these full text search engines completely integrated into the database engine? There's lot of problems with keeping the data in another system such as Lucence, including keeping the data up to date, and the inability to join the results with other tables. Is there a specific technological reason why these two technologies can't be integrated?

share|improve this question
    
Another good question would be why don't they just buy and integrate one of these existing technologies, rather than bust their butts developing their own competitor? –  FrustratedWithFormsDesigner Jun 16 '11 at 19:39
    
Exactly, and many good full text indexes are open source, which may (or may not, depending on the license) allow them to integrate without actually paying for anything. –  Kibbee Jun 16 '11 at 19:53
    
The question gets a -1 because the term 'Good' is completely subjective and frankly the basic premise of the question may not be valid, and a vote to close as 'Not Constructive' by suggesting companies are 'lazy' because they dont make something specific that you personally want. –  GrandmasterB Jun 16 '11 at 20:09
2  
@Grandmaster: Touchy, aren't we? While the question may not be worded exactly the way you like, the premise of the question is valid. I upvoted. –  Robert Harvey Jun 16 '11 at 20:15
1  
@FrustratedWithFormsDesigner: Actually, in 1987, that's exactly what happened with our product. Plexus was trying to morf from being yet-another-UNIX-box-vendor into a document management company and they convinced Informix to license our IR technology for inclusion with their RDBMS. Talk about your culture mismatches! The cognitive dissonance was like being Best Werewolf at a marriage between a goldfish and last Tuesday. –  Peter Rowell Jun 16 '11 at 23:52
show 3 more comments

5 Answers 5

The short answer is because text retrieval has almost nothing in common with how traditional databases are designed and used. Someone who is an ace at creating/using an RDBMS is like a lamb to the slaughter when they approach text retrieval for the first time.

(Sorry for the long answer, but I'm sick in bed today and I've got nothing else to do.)

The following could easily come under TL;DR, but if you have the time and the interest, what follows is a piece of the longer answer. Note: I'm speaking from having implemented a commercial information retrieval system starting in 1986. We were a technical success, but a marketing flop.

Doing IR (Information Retrieval) properly requires that you begin by thinking about what you are searching for and how you will find it using your query mechanism. This may sound easy, but it is anything but easy. Here are just some of the things you will have to decide before you even begin scanning your documents (or fields).

  1. Does case matter? Is DoD the same as dod? How about "flame" and "FLAME" (a cologne based on the Burger King Whopper (yes, really)).
  2. What kinds of tokens will you index? You obviously want to index "daddy". You probably want to index "daddy123". Do you want to index "123"? "12.3"? "192.168.1.1"?
  3. How do you deal with things like hyphenation? A somewhat out-of-date example is "data base", "database", and "data-base", all of which were in use concurrently in 1986.
  4. If your query language supports the concept of "Find A in the same sentence as B", how do you determine sentence breaks? Although '?' and '!' are easy enough, those '.'s are a bitch. Think about things like "Mr.", "2.", "etc.", etc.
  5. Are you going to support stemming? If so, how careful will you be to not accidentally change the POS (Part Of Speech)? E.g. "cats" can stem to "cat", but "blinds" may or may not stem to "blind". If it was a verb ("He blinds me") then you can stem, but if it was a noun ("I like your blinds) you can't (or at least shouldn't). Stemming is very seductive, but it is a swamp of the First Order.
  6. What languages are you going to support? What works in English can fail big time in either French or German, although strangely enough it will tend to work OK for Japanese in the Hepburn Romanji representation.

And the list goes on and on.

Then we have to think about our query language. It may seem that if all you are going to support is simple Boolean then it should be easy, but the one thing that is pretty much universally agreed upon is that pure Boolean sucks for text. For example, you will need additional operators to specify ordering and proximity, and boy, oh, boy does that ever make life more complicated. You also need to know what section you are in -- title, header, body, etc. -- which leads to all sorts of collection-specific parsing fun. But now it's no longer sufficient to just have a list of tokens that occur in the doc, you have to know where in the doc they occur. This results in an address tuple of (docID, sectionID, para-in-section, sentence-in-para, word-in-sentence). Efficiently storing and searching this information can get gnarly for a non-toy collection.

Then there is the actual structure of your data store. Text systems are normally implemented as a "full inversion" of the documents. How many indices does the average DB have? 10? 50? 500? In IR it is not uncommon to have 5,000,000 or more indices, one for each separate token. And any given token may have 1 instance (eg. "narfle" or "garthok") or 10,000,000 instances (eg. "the"). This means that your whole method for creating and updating indices has to be lightning fast or you are going to sink into the swamp. And you still have many of the other problems that a traditional DB does: disk space management, crash recovery, coherent snapshot from a running system, etc., etc.

Finally there is results ranking. An unranked result set from a Boolean query against a large collection is useless to a human. It might be useful to a program, but that was not what I was dealing with. Although our system implemented Boolean, our selling point was that we were the first commercially available system to support similarity searching, based on the Cosine Coefficient. The math and logic of this type of search (basically a normalized dot product of the query vector against millions of document vectors) required radically different approaches to data representation and storage than did Boolean -- definitely not something available in your average DB.

All of this (and more) is why "text retrieval" and "database" almost don't belong in the same sentence together. I think you would be better off picking a good database for your "normal" needs, and then using an external IR system to index/search the "documents" in your primary DB.

share|improve this answer
    
+1 Hope you get better soon. ;) –  deceze Jun 17 '11 at 4:56
add comment

Oracle has pretty sophisticated full text searching capabilities as part of Oracle Text and has had that for more than a decade. SQL Server 2008 supports full-text search as well. So I'm not sure that the premise of your question is correct.

If your question is really more along the lines of "why don't we do more full-text searching in databases rather than in middle tiers", there are a few factors. Database developers generally want to store normalized data not unstructured or semi-structured data. So they would generally prefer to design systems that parse the incoming data into separate searchable fields rather than supporting full-text search. Application developers also tend not to want to store unstructured or semi-structured data in CLOB/ BLOB fields in the database because they view it as easier to store the data on a file system and don't want the database to get too big. I'm not a fan of this argument, but it's a common one. As a result, most people end up with the data they'd want to do full-text searches on living outside of a database so it needs to be indexed outside of a database. If even a reasonably small fraction of your data lives outside the database, having the middle tier index it becomes a much more palatable solution.

If you store your unstructured and semi-structured data in Oracle, I'd put Oracle Text up feature-for-feature with any of the standalone full-text indexing solutions.

share|improve this answer
2  
Yeah, after looking at Oracle Text, it seems to have a very good feature set. So many the question is, why don't others have such good support? –  Kibbee Jun 16 '11 at 19:47
    
+1 Good points. I would also add that there are many intricacies such as pluralization that complicate effective full-text searching, intricacies that are not part of the core competencies of most RDBMS's. –  Robert Harvey Jun 16 '11 at 19:48
    
@Kibbee: It's probably one of those things that's easier said than done. And maybe Oracle customers are more willing to pay for Oracle to invest in the R&D than customers of other RDBMS vendors. –  FrustratedWithFormsDesigner Jun 16 '11 at 19:59
    
@Kibbee - Oracle also invested much earlier and much more forcefully in the idea that it makes sense to store unstructured and semi-structured data in the database. Most of the other vendors are much more focused on storing relational data and are relatively late comers to the "store all your data in a relational database" party. –  Justin Cave Jun 16 '11 at 20:09
    
Oracle is also one of the most (if not the most) expensive and popular databases out there. They can afford to pay a lot of people to work on these features, whereas other companies may not have the budget. They also are almost exclusively developing databases, so they have a greater interest in developing features like this. –  Michael K Jun 16 '11 at 20:10
show 1 more comment

I've never had a lot of problems with FTS in PG.

http://www.postgresql.org/docs/current/static/textsearch.html

That said, it's not sphinx or lucene, or whatever. I think there are a few main reasons (some pointed out above). I think the only one they missed would be the cost factor.

FTS isn't free. It takes memory, cpu and disk resources to search. Databases usually have enough work involved without doing FTS. Scaling 1 database that does FTS and structured data storage is usually painful. Scaling separate things (lucene/sphinx/whatever) and Scaling a database is usually less painful.

Mostly it's around sizing, and what your needs are. Trying to build something like Google (or broad web search) with PG's FTS or Oracle Text is asking for trouble.

I use PG's FTS features in a production environment, but I keep the stuff I want to search fairly small/limited. I'm not searching word documents, I'm searching whole records (a combination of DB rows). For instance one of our search functions is searching for people. In our DB, we want to store their names in separate places (first_name,last_name,etc). Plus many people have more than 1 name (I know it might sound crazy, but it's totally true). Plus many people want their umlauts and what not non-ascii characters in their name respected (say when printed on their check), but nobody will remember how to type in the umlaut to find the person, so we let you search against either with or without and usually find the person you want.

Even with multiple names, and storage of plain ascii and UTF-8, we aren't talking about a LOT of search space AND the data is already in the DB(where it belongs), so doing it within the DB makes TONS of sense.

But pushing HR's 1 million word documents into a DB just to use FTS on them doesn't make sense. They are already files on the filesystem, and the filesystem does a better job than a DB could of keeping that data safe and sane, so let's use Lucene, or sphinx or whatever to search that data.

Use the right tool for the job! But to say DB's don't have FTS isn't true, but the use case I believe is different.

share|improve this answer
add comment

Most applications of a database don't need full text searching.

If it was built in it would still face the same issues that an external indexer would, you would just be paying for it (in time/space/cost/complexity) whether you needed it or not.

share|improve this answer
2  
MySQL, MS SQL Server, and Oracle all have many features that are not needed by most applications of a database... and many of those features seem at least as complicated as a good full text search. –  qes Jun 16 '11 at 19:39
add comment

Full text search is not the point of a relational database management system. Heck, there are lots of holes in the relational part. (Have you read Chris Date's book?)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.