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!