Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

closed as too broad by gnat, Bart van Ingen Schenau, GlenH7, MichaelT, ChrisF Nov 7 '13 at 22:45

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

Google has an api for address search. Have you tried it? And what is it your are wanting to search for? Best bet for you is probably going to be indexing that. – Chad Jul 19 '11 at 13:11
up vote 10 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
+1 For a neat idea, but the view performance might suffer with larger table joins. – maple_shaft Jul 19 '11 at 13:41
FWIW, we use a similar tactic but we use triggers to build the search fields on write. Works great though there is some storage overhead. – Wyatt Barnett Nov 6 '13 at 14:26

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.

share|improve this answer

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.
share|improve this answer

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