8 weeks. Eight-Freaking-Weeks!
8 weeks ago I embarked on a journey that would change my life. Little did I know at the time just how much this journey would affect me physically, emotionally and psycologically.
Lets go back a further 2 weeks. I started a new job as Senior Developer for OMG. We have a 'legacy' codebase for our CMS (Content Management System) which works, but needs some performance improvements. I have been given the task of fixing up this codebase. One of the areas that requires a lot of enhancing is our search engine, but its not quite as simple as writing a little query in SQL such as 'SELECT blah FROM table WHERE name LIKE "%search term%"'... Oh no, its not quite that easy.
Think 31,000 domain names, each with an unknown number of categories, each having either category niche (auto's for example) or a specific location match (Sydney, for example). Then, throw in almost 2,000,000 listings to appear on these sites in an ordering specific to their account type and account data and make sure that Tasmanian Lawyers do not appear on our Perth Plumbers site...
Enter an enthusastic new employee wanting to make his mark and you have an 8 week journey through 19 hour days, crashed servers, day-long data rebuilds and many more exciting activities.
We decided to build the search engine in Sphinx which is a full text replacement for MySQL, I wont go into the details of how we did this, but I will say that the code and indexes have been rewritten about 6 or 7 times inclusive. I have worked on this, day in and day out for 8 weeks. That is the longest time I have ever spent on a single component and a few times I have been curious about whether my boss thought he'd made an incorrect decision hiring me. More of my hair is grey now than it was 8 weeks ago and my wife is questioning my commitment to our marriage after displaying willingness to stay up and work rather than come to bed.
Last night, we pushed the latest changes to a staging server and replayed Apache logs (from a period when traffic was destroying our servers) to test performance... and it passed. To say I am happy is an under statement, we went into this to increase performance and to remove as much as we can from MySQL (for various reasons) and a few times there I was unsure whether we had just wasted our time, including one time when I made the suggestion that we use a different Full Text Indexer (such as Xapian.) But today, the finish line can be sensed, a term search across 1,900,000 rows is taking 0.07 seconds which is awesome, which only one or two queries in MySQL.
This is why I am in development, this sense of satisfaction you get when, after weeks of nutting something out, it works... and works well. Hopefully I'll still have a job now when my probation period is up next month.
I will be making a post about my findings with Sphinx and how to make it work well when you have multiple criteria to search within on Fliquid Studios in the coming weeks (once I am absolutely sure that this is working ok.)
