.NET Discussion

.NET Issues, Problems, Code Samples, and Fixes

ASP.NET and MySQL: How To Build A Search Engine For Your Website In 3 Steps


About a year ago I built a fully-functioning search engine for my website, Columbus Supply. It took me all day to figure out the SQL syntax, but afterwards, I was very pleased with the results.  MySQL has very powerful search capabilities with the MATCH() and AGAINST() functions when used in conjunction with the FULLTEXT attribute. Using these, MySQL determines a “score”, which is a relative relevancy to the search query. 

The technique I used requires a few queries, but only one call to the database if all the queries are strung together and sent at once (separated by semicolons, of course 🙂 ). First, create a temporary table with all the necessary columns from all your different existing tables:

CREATE TEMPORARY TABLE temp1 (PlayerID INT, Name TEXT, Age INT, Biography TEXT, FULLTEXT (Name, Biography)) ENGINE=MyISAM;

Then fill your temp table with all the data from your tables. Be sure to do any replacing and concatenation here:

INSERT INTO temp1 (PlayerID, Name, Age, Biography)
SELECT PlayerID, Name, Age, REPLACE(Biography,'</p><p>')
FROM tblPlayers;

Now you are going to do a SELECT statement against that table for all the columns you want. The real magic happens in the WHERE clause:

SELECT *
FROM temp1
WHERE MATCH(Name, Biography) AGAINST ('YourSearchString')

Remember how we declared ‘Name’ and ‘Biography’ as FULLTEXT? Here’s where that comes into play. You are selecting everything, but you are comparing against the columns you deemed as FULLTEXT. With the MATCH() and AGAINST() functions in the WHERE clause, the results are automatically ordered by relevancy. If you want to see the actual relevancy, just include your MATCH() and AGAINST functions in the SELECT statement using AS to name it something, like “score”.

And that’s it! You can take those results and populate a GridView or any other control. It can get complex with all the unions and joins from all your tables, but this is the concept that I came up with. It has served me well, and my results are relatively accurate. You can extend this further with filtering in your WHERE clause, such as WHERE Age=24 AND MATCH(...

NOTE: This will not return a result for some queries for various reasons, including but not limited to: the use of common words that MySQL has deemed semantically negligible, searches of three letters or less, or searches where the search string appears in 50% or more of the records in the particular dataset you are pulling from, ie, your temp table.

In the instance where a search is three letters or less, you can do the first two steps as usual, and in the last step, instead of using MATCH() and AGAINST(), use LIKE like so:

SELECT *
FROM temp1
WHERE Name LIKE '%YourSearchString%' OR Biography LIKE '%YourSearchString%'

Granted, this will result in a much less accurate recordset, but it will still yield some relevant records, which may be enough for your user. They did, after all, enter a three-letter-or-less query!

More reading on this technique: Full-Text Search | Fine Tuning Full-Text Searches

Hope this helps someone out there!

Advertisements

September 20, 2007 - Posted by | ASP.NET, GridView, MySQL, Tips & Tricks

4 Comments »

  1. This is cool, But now a days you can you google or yahoo search in our websites.

    Comment by Raj | November 21, 2007 | Reply

  2. Raj –

    True, you can, but you are limited to what they can find on your site. When you build your own search engine like I did, I can search fields in my database not made available to a generic Yahoo! or Google search engine. Also, you have greater control over how the results are displayed, and you don’t have to have ads/Google/Yahoo logo on your site if you don’t want 🙂

    Comment by Some.Net(Guy) | November 21, 2007 | Reply

  3. Good point Raj.

    Thanks for the info!

    Comment by Yaarik | June 19, 2009 | Reply

  4. Thank alot….

    Comment by Cavan TAN | March 24, 2010 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: