Best algorithm/practice when creating a search mechanism for your database? [closed]

Question

I have been designing a database where it is very important to provide users with a good search mechanism. So I was wondering what some of the best practices are for using keywords to search over multiple database tables and return the relevent records?

Some other things I am curious about:

• The users location, if they provide an address
• The speed of the algorithm

Additional Information: I am using C# and LINQ-To-SQL.

Answer 1

There are basically three options (in addition to using naive "LIKE" statements):

• Full text search, if your database allows it
• Sphinx, Lucene, and similar options
• Google/Yahoo/Bing custom search and APIs, if applicable

My own preference goes to full text search (it's quite good and highly configurable in Postgres). Many devs prefer Sphinx or Lucene -- I've never found the need to use either, but see this post for a second opinion.

For user location, some kind of GIS infrastructure (e.g. PostGIS in Postgres) is useful if you plan to extract relevant information in a timely manner (e.g. collect stats on users in a certain area/region). I'm not familiar enough with Sphinx and Lucene to know what the latter two have to offer on this front.

Answer 2

It depends on the situation and what you are trying to search against. I remember we implemented a search solution for a customer by creating a database view that concatenated several fields from different tables into one field and we ran our search against that view.

Answer 3

While all of the above are important the most important thing in relation to providing a good search mechanism/experience is the database (and maybe the gui) performance.

In terms of database performance you need to ensure you search columns are correctly indexed, that statistics are current, that your SQL is using current query plans and that your database has sufficient memory and a decent CPU.

In terms of search result presentation you need to ensure the results are displayed in a timely manner. No point in having the database return results in <1 second but the gui is taking 2 minutes to display them. You need to ensure the display code is optimised and maybe supports paging.

Answer 4

To get a better search, the following solutions can be implemented :

• Make proper indexing of the column depending upon the search conditions. Also keep in mind that the number of indexes must be optimum, other wise it will overburden the database.
• Define the primary keys for the column in the table.
• If the search is too frequent then try to define the full text search option at the time of creating the database.