Create Your Own Search Engine With MySQL
I discovered this trick the other day. I was creating my "Related Posts" feature on my postings and realized I could use this to do implement a search engine on my own site.
The trick is to use the Match...Against syntax implemented in MySQL 4.x and higher. MySQL document is on their site page Fulltext Search.
Create Indexes
You need to have FullText indexes on the columns that will be included in your search. If you are using a web hosting company then they will most likely have a web interface to easily do this. Otherwise you will need to use SQL commands to add it.
Once indexes are created, then you will be able to search these columns quickly.
Syntax
Here is a basic query. This is for my blog. I want to search the columns title, keywords, and postcontent for the given [searchstring].
SELECT post_id,title FROM posts WHERE MATCH (title,keywords,postcontent) AGAINST ([searchstring])
The result returned will be all the articles that correlate according to the MySQL algorithm sorted with the most correlated rows at the top.
You will notice I have a search field at the top of my left menu. The search string you enter goes to a page that executes the above query and returns the results sorted with the most strongly correlated at the top. From their I create links, summaries, and whatever else to make the page look nice.
Try it out and see if you like the results.
If you enjoyed this post, then make sure you subscribe to my RSS feed or subscribe for email updates. Only one email a day and only if there was a new post.
Related Posts
Added Related Post Links to Each Post
Add a Title Property to Your Links
Add A Recent Articles Section To A Blog
Finding Music Files Using Google
Adding a Sitemap
Category: Blogging
0 Comments
No approved comments yet.