Optimizing Searches Across Many Columns

[HartleySan]

For a site I'm designing now, I'm looking to create "one search bar to rule them all."

Specifically, I'm looking to create a single search bar on which anything can be searched for without the use of a combo box for selecting categories.

 

My concern with this though is that for every search that's performed, about 5-6 different columns will have to be completely searched for the keywords that are entered, which I can imagine being rather inefficient.

As such, what's the best way to optimize searches that are performed across all the records in multiple columns in a DB?

 

Thank you.

[Larry]

You are right that the multiple columns would be a hindrance. The solution, then, is to get out of the multiple column structure. Similarly, the search as written would be a LIKE search, which is much less efficient than an equality search. Or, if an equality search is not possible, then a less onerous LIKE.

 

The solution to both of these problems is to turn to a conventional search system by indexing your content (everything in the five or six columns) and then running the search query against the index. I explain how to do that at the end of this article: http://www.peachpit.com/articles/article.aspx?p=1802754

[HartleySan]

Thanks, Larry, as always.

I will read that article later, and then response accordingly.