LGF Database Conversion Hijinx 5
Tue, Mar 27, 2007 at 1:20:27 pm PDT
Some more books on my desk:
Most of the Blog Engine is now refactored to use MySQL. (Not on the live server; on the development server.) I saved what I thought would be the hardest part for last: the LGF search feature. But MySQL’s built-in search capabilities are not only much simpler to implement than the existing brute-force “scan every file looking for a match to a string” approach, the MySQL search will be much more powerful, and a lot faster.
For the database nerds, I’ve added FULLTEXT indexing to the entries and comments, so our new search module will be able to give you much more intuitive results, similar to a search engine such as Yahoo or Google. To get even more technical, we’ll have keyword-based “boolean” searches, where you can choose which words to include or exclude from the search.
For example, search for:
Osama bin Laden
...and you’ll find posts that contain any of those three words. A post that has the word “Osama” would show up, even if the other two words weren’t present.
But put a plus symbol in front of each word you want to include:
+Osama +bin +Laden
...and now the search will find posts that contain all three words.
The minus symbol does the opposite; it says, “don’t include this word” in the search.
Results can also be sorted by relevance, and you can even specify the relevance of a search keyword, by using the ‘>’ and ‘~’ operators. There are other boolean operators too, for wildcards and simple pattern matching, but that’s for another post. (If you can’t wait to find out the wonders that lie ahead, here’s the MySQL Reference Manual: Boolean Full-Text Searches.)
And speaking of “faster,” doing a FULLTEXT search through 3.6 million LGF comments for the term “+Osama +bin +Laden” returns 5,157 results.
And it takes 0.0259 seconds. That’s right. 3.6 million comments in two hundredths of a second.
I know, it’s a bit slow. I’m working to optimize it now. (I kid.)
UPDATE at 3/27/07 1:31:33 pm:
Oh, and I should mention, so no one gets the wrong idea — none of this incredible newness is actually live on the site yet. The whole house of cards is in final testing on the development server.
