This post is a bit late as it describes things that happened last Thursday, but it was a busy weekend and all my “posting to stuff” energy got sucked up by twitter.

I knew it was risky to make a self congratulatory post about a feature that had just launched. All and all it was pretty successful and took a deent load off the server, but two bugs cropped up that were sever enough to force me to revert the code.

The first issue will be easy enough to fix. Our app has several features, including a property slideshow, that are called remotely via javascript includes which also rely on the results class. Because the caching mechanism needs a PHP session id to avoid having one user contaminating another’s search results these tools stopped returning any properties. Luckely I wrote the constructor of the results class to have an all purpose override array as one of it’s parameters. So all I need to do to fix this issue is to generate a sessionID for the javascript includes to pass through to the results class and they should work again.

The second issue is going to take more work and creativity. When I worte the results class I built out the featured properties function to be generic. My thinking was that any property lising that is owned by one or our clients is a featured property and thus belongs on the featured properties page. Our clients, however, seem to have disagreed. They’ve figured out that they can append search variables to their featured property URLs and do things like make featured properties pages that are only for million+ dollar homes, or just commercial listings, or… whatever they want. This is all fine and good when the featured property search is repoerformed each time the page is called and is user agnostic, but not so much when my caching mechaism was in place. The caching mecanism treats all featured properties searches the same effectively ignoring any search terms added on.

I reverted the the previous version of the results class from our SVN repository and all went back to normal. Once I tie up these last couple loose ends I’ll be able to push the caching mechanism back out as part of Wednesday’s doubledot release. Here’s hoping it goes better this time. The caching mechanism did seem to take a noticeable load off the server, so it seems like a worthy endeavor to retry.

In the last couple of days I did some work to complicate the IDX application a bit. I applied the patch today that contained the changes and so far all seems well. Here’s the story.

About nine months ago I completed a reworking (aka complete rewrite from the ground up) of the application’s results class. This is the code that assembles all the properties that meet the criteria of the search that has been performed and makes them available for what every they need to do. Once all the various data tables had been queried the matching results were placed in a temporary heap table so that they could be sorted, filtered (based on client preferences and/or MLS rules), and truncated if need be. I decided to use temporary heap tables because they’re fast and since they’re session specific I knew that I wouldn’t have to worry about one user contaminating another’s results.

The system has been working beautifully for these last nine months but as our traffic has grown (now upwards of 44,000 hits a day) mySQL was having trouble keeping up. All the heap tables we using a lot of the server’s RAM and since the heap tables were being destroyed as soon as the page was delivered searches had to be rerun completely just to move from page to page.

Todays patched changed things. The heap tables are gone in favor of a searchCache table (one for each client in our system) where all search results end up. When the same search is run again (like when switching pages) the results can be pulled from the cache instead of all the data tables needing to be queried again. All results are tagged with the users PHP session ID to prevent result contamination and every 4 hours the cache is cleaned to prevent the tables from getting too large. Featured property searches are also cached in our system and, because they are the slowest queries we perform^*, they are cached for 24 hours until we get new data.

I’m pleased so far. The patch was uploaded to our server 8 hours ago and thus far there are no reports of problems.

Thanks to bob the lomond for the photo.

^{*Featured results are the slowest because of the number of tables that have to be queried. Normal results only have to query 1 table per MLS being searched because they are property type specific. Featured properties are property type independent and thusly upwards of nine tables per MLS may need to be queried.}