Search Query Optimization

Question

I haven't ever dug into cleaning/reformatting search queries too much in the past, at least not more than general security things like preventing sql injection.

I am realizing that I should be implementing keywords like AND, OR, NOT, etc... and doing things like clearing punctuation such as apostrophes, hyphens, etc... As when a user types "Smiths" in a searchbox, the query would not return "Smith's" (with an apostrophe).

What other things can I do to improve my user's search queries (without being damaging to them)?

I am coming from a PHP MySQL-FTS setup; however, I'm sure that this could be extended to multiple platforms.

EDIT

Let me clarify that I'm not so interested in the SQL query to the database, what I'm interested in optimizing is the query that the user provides in the search box.

Answer 1

1. Create an index on the "where" clause columns of your search queries.
2. To enable naive spell Correction perhaps, you could also store the soundex of the column you would like to offer spell-check for.
3. Enable logging for slow-queries which would help you in tracking down performance issues.

Answer 2

• NEAR keyword
• double quotes for "exact phrases"
• remove short/common words ("a", "an", "the", etc)
• stemming (remove common prefixes and suffixes)

I'd suggest reading through the answers to this similar question: http://stackoverflow.com/questions/862518/optimizing-a-simple-search-algorithm and also this article on some of Google's features.