Sometimes we get tied into specific requirements rather than leveraging some agility and creativity to determine what’s really needed. Often by using this creativity we can improve performance, reduce load on our database, and users understand that perfect data isn’t always available.

A great example of approximate data shows up when you do any search through your favorite search engine. Both the number of search results and the number of pages are approximations. Sometimes you’ll find as you go to switch from page 3 of search results to page 4 that the search engine finds out there aren’t actually any more results to show. Alternatively, as you page through the results the number of pages can decrease. In both of these cases, the performance and accuracy of the information is tuned to the needs of the users.

Data shows that more than 50% of users only click on the first search result after conducting their search. When you add up the entire first page of results accounts for >90% of clicks to results. To more than 90% of users, the number of pages of further results are almost entirely irrelevant. Search engines have realized this and prioritized the computation of these values.

At iContact, we just had an instance where we decided to change the user experience a bit to improve performance, however I doubt our users will ever notice. When you click the “Create” tab inside our application we’d designed it so that if you had yet to create a message, we would hide the “Re-use a sent message” link. The challenge was that this created a relatively expensive query that demanded a temp table and filesort. When we went to optimize the query, we realized a few things:

  1. The only people who wouldn’t see the link are people who just created their account.
  2. We incur the cost of checking for this message to load an otherwise static page mainly used for navigation.
  3. The cost to run the query gets more expensive the more messages you send and the less valuable.

We decided to make the link show up all the time. This eliminated the query on one of the most common pages loaded in our application. It’s reduced its page generation time by 100s of milliseconds on average.

What are ways you can get creative with your application to alleviate database hotspots?

  1. Can data be approximated?
  2. Can you update less frequently, perhaps even probabilistically?
  3. Can you stop showing the data at all?
  4. Can you email the data to the user so you have more time to process it?

What other suggestions to you have for changing application logic to create more performant applications? Any case studies out there?