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 addressThe speed of the algorithmAdditional Information:I am using C# and LINQ-To-SQL.
Best algorithm/practice when creating a search mechanism for your database?
c#;database;linq
There are basically three options (in addition to using naive LIKE statements):Full text search, if your database allows itSphinx, Lucene, and similar optionsGoogle/Yahoo/Bing custom search and APIs, if applicableMy 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.