best way to search data-base table for user input keywords?

Question

I want to build a product-search engine.

I was thinking of using google-site-search but that really searches Google's index of your site. I do not want to search that. I want to search a specific table (all the fields, even ones the user never sees) on my data-base for given keywords.

But I want this search to be as robust as possible, I was wondering if there was something already out there I could use? if not whats the best way to go about making it myself?

Answer 1

As far as stuff that's already out there, take a look at these :

• Search all tables (for SQL Server, but you could probably adapt it to MySQL)
• Another search all tables (for SQL Server, but you could probably adapt it to MySQL)
• Search all varchar columns in database
• MySQL Full-Text Search
• Using MySQL Full-Text Search

Answer 2

You can try using Sphinx full-text search for MySQL.

Here's also a tutorial from IBM using PHP.

Answer 3

I'd focus on MySQL Full-Text search first. Take a look at these links:

• http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html
• http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

Here is a snippet from the first link:

Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a literal string, not a variable or a column name. There are three types of full-text searches:

Answer 4

SELECT * FROM table WHERE value REGEXP 'searchterm'

Allows you to use many familiar search tricks such as +, "", etc

This is a native function of MySQL. No need to use go to a new language or plugin which might be faster, but is also extra time for maintenance, troubleshooting, etc.

It may be a little slower than doing some crazy C++ based mashup, but users don't generally notice a difference between milliseconds......

Answer 5

One thing you might also want to look into (if you're not going to utilize sphinx), is stemming your keywords. It will make matching keywords a bit easier (as stemming 'cheese' and 'cheesy' would end up producing the same stemmed word) which makes your keyword matching a bit more flexible.