Full text search from the database

Question

Hi: Suppose I have a MySQL database named DB, and a table named ContactInfo. The following is its structure and some example data(Just one item):

Table----ContactInfo:

---

-----------------------------------------------------------
name  fullname                                       phone
-----------------------------------------------------------
NASA  National Aeronautics and Space Administration  00000
-----------------------------------------------------------

---

Now I want to search the data under the table "ContactInfo" using the following manner:

1) No matter I enter "NASA" or "National Aeronautics and Space Administration" or the phone number, the right line(in this exmaple, just the first line) should be returned to me.

2) When I enter "National Aeronautics Administration" or "National Space Administration" or "American Aeronautics and Space Administration" or "American NASA" which does not have a exact match in the database,but the first line should be returned to me since the content in the first line are related to the requested string.

The manner two can be seen as the obscure search.

I have considered to use the "Full context search" such as the "Hpyer estraier" and "Lucene". However I found that my requirement is different more or less.

When using the "Hyper estraier" or "Lucene", the indexes shoule be built first according to the parsed plain text. Then start to search.

So does that mean I should change the data in my databse to multiple plain text files(each record in the table to a single file), and then build the index according to these files?

BTW, I just found that the MySQL support full text search, however we have to handle some Chinese characters, but it seems that mysql does support chinese characters full text search perfectly.

So, anyone can give me a suggestion?

Answer 1

You could use something like this for that purpose:

SELECT *
  FROM `ContactInfo`
 WHERE `name` LIKE '%your-query%'
    OR `fullname` LIKE '%your-query%'
    OR `phone` LIKE '%your-query%'

In order to support your case #2, you need to replace all spaces in your-query with %-characters to allow any combination of characters between the entered words. Remember to escape the users input to prevent SQL injection.

And putting all your data into (separate) text files appears to be really ineficcient for storing data.

Answer 2

You can use sphinx in conjunction with mysql. sphinx is a standalone tool but it has a mysql proxy mode where it basically behaves like a mysql server and can be queried with any mysql connector out there. Or, as an alternative, there is a sphinx storage engine plugin for mysql which allows you to query sphinx with your current mysql connection and perform join's to other tables.

Definetly not a quich and dirty solution but it's worth a further look: http://sphinxsearch.com/