Tom Mollerus' Weblog

I have to admit that when I posted an entry a few days ago on creating indexes to make your database queries run faster, I made a small error of omission when I suggested that indexes be created for every two columns that are used in a join. The fact is that you should create indexes even for single columns if they're the main lookup in the query. From the MySQL documentation:

Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially.

The other day I found myself scheduling a task which would send out financial reports to users on a daily basis. The task really didn't need to run on the weekends, but there's no way to indicate that in the CF Administrator-- the scheduling form it gives you either lets you run at a specific date and time; daily/weekly/monthly at a specific time; or daily at a specified interval. There's nothing that will let you run a task only on certain days of the week.

Luckily, that's easy enough to control within your scheduled task itself. All you have to do is check to see what day of the week it is and abort page processing if this day is one on which you don't want to run the task. If you check the calling user agent, you can even give yourself the flexibility to call the task to test it on the days you don't want it to normally run. Sample code is below:

<!--- If this page is being called as a scheduled task and the current day is Saturday or Sunday --->
<cfif CGI.HTTP_USER_AGENT eq 'CFSCHEDULE' and ListFind('1,7', DayOfWeek(Now()))>
	<!--- Abort the page with a message --->
	This page doesn't run on weekends.<cfabort>
</cfif>

How many times have you tried to test a scheduled task that generates emails to your site's customers-- say, a nightly message to those customers whose subscriptions will expire soon-- and accidently sent the email off to customers? I've done it too many times to count, so I've had to develop a few practices to help me test my tasks without actually performing any actions I don't want to happen. At the same time, these practices have helped me learn how to protect my tasks from malicious users and log a task's results for later review.

I was taught a simple lesson in the power of database indexes today. Here's how it happened: for a while, one of the sites I'm responsible for seemed to become slower and slower over time, even to the point of timing out during a database query. I always figured that it was because the web server was over 5 years old and needed a re-install or something like that. So, I switched to a new server and migrated the database to the new machine, which has more RAM and an has a more modern version of MySQL.

But guess what occurred on the new server when I started testing on it? The site and its database were as slow as ever. At that point I could no longer blame the problem on the machine, so that left... me. I had to figure that code optimization and/or database optimization were the only issues that could be at fault. So, I fired up a browser and looked at the MySQL docs. I knew enough to start investigating indexes to get things working a little faster, so one of the first lines I saw was this:

Use of indexes on the relevant columns is the best way to improve the performance of SELECT operations.

I'm definitely no database administrator, but my suggestion would be to create a two-column index for each join that you perform in your application. One column would contain the primary key of the table that you're indexing, and the other column would be the foreign key in that same table.

For instance, to create an index on a table of users that you join to a table of accounts via a foreign key in the users table, you'd write a MySQL index like so:

CREATE INDEX users_accounts ON users (userID, accountID)

Do this for each of your joins and your database should be nice and speedy for a good, long while.

After months of poring over CFCs for non-var'ed variables or other problems, I've finally solved the mysterious errors we've been getting on our production site at work with the help of Ray Camden's post "NPEs, onRequest, and other mysteries of the universe". If you have ever gotten intermittent ColdFusion errors with the message "null null" or "invalid query parameter binding" in lines of code that you know are correct, then you've run into this maddening problem.

Ray's blog refers to the experiences of one of his readers, who after seeing unpredictable null pointer errors in his site is advised by his web host's support tech to add a very basic onRequest function to his Application.cfm file. This problem sounded nearly identical to the one I've been tearing my hair out over for months (well, I stopped tearing my hair out over it and just started abiding with it months ago). I already had an onRequest function in my Application.cfc file, but I decided to mimic the one mentioned in Ray's post as closely as I could, so I added the <cfreturn /> call to my code. And do you know what? The errors stopped!

So if you've ever seen "null null" errors in your code, take a look at your onRequest. I don't know why this solution works, but I'm glad it does. Does anybody know why the NPE's happen, or why the inclusion of <cfreturn /> seems to solve them?

At work I've been preparing a new web service for our licensees to use. It's not just a content feed, like most examples of web services that you hear about; instead, it's a financial tool, where the licensee offers the tool's interface on their site, calls our site with the user's data, and then displays the calculated results that we send back. I decided that in order to make the licensee's own development work go smoothly, we should test out the new service ourselves from another server in another programming language-- we should eat our own dog food, in other words. The challenges that we found was in generating and serializing complex datatypes in PHP, and in deserializing those datatypes back to structs in our web service. Here's how we resolved it.