Large date range queries now 400-900% faster for the average site

One of the biggest complaints we receive is how long it can take to look up historical data that spans a range of more than a couple of days. I want to explain why this was and what steps we have taken to fix this.

Previously we had only been storing summary data on a per-day basis. So if you wanted to view an entire month's worth of searches for, say, July 2009, our system would have to do 31 queries (one for each day) to get all of the searches for the entire month, then sum all that data together before outputting anything. This is inefficient and could take quite a while.

To fix this, we have added systems that also summarize all of your data on both a weekly and monthly basis. So now if you do a query for July 2009 searches, it only has to do one database query, instead of 31. However, because there is more data in that single query output, the time it takes will be longer than just 1/31 of the original. For the average site it will be about 10-20% of the original size of data. (This is relevant because the more data it has to read off the disk, the longer it takes to get it). This means for the average site, large date range queries will take just 10-20% of the original time that they used to. That's a huge improvement!

The same thing applies to full week (Sunday - Saturday) queries. Instead of having to do seven queries per week, it only has to do one. And all of this works fine for spanning multiple weeks and months also.

(We've been running scripts in the background the past week or so that have been calculating summarizing this data into weekly and monthly chunks, all the way back to Jan 1. All servers are done with this, except db7, which should be done within 24 hours. At the end of each day going forward, it adds your daily totals onto the week and month that that date belongs to. This means that for data prior to Jan 1, these optimizations will not apply).

What if your date range doesn't fit exactly into full month or full week increments?

No problemo. We have designed this system to be very flexible, using the most optimized queries possible for whatever query you throw at it. Let's pretend instead of July 2009 (1-31), you queried for May 31 - July 15.

Here's how our new system deals with this query. This is a bit technical, but it's worth a read to learn how the system optimizes the queries, which will also allow you to create date ranges that are as optimized as possible.

First, it figures out all of the individual days in this date range. In this case, there are 46 full days. This is how many queries would have to be done under the old system.

Next, it checks if there are any full months in this date range. In this case there is one (June 2009), so it removes all of June from the daily queries, and adds that month to the monthly queries. We are now down to 17 queries, from 46.

It then checks if there are any full weeks (Sunday - Saturday) in the remaining single days. In this example, July 5-11 is the only full week. So it removes those 7 days from the daily queries, and adds that week to the weekly queries.

We are now at 11 queries - 1 monthly, 1 weekly, and 9 daily. This is down from 46 daily queries originally, which is ~75% less queries that need to be run, which will result in much faster response times. Hope you enjoy.
9 comments |   Aug 18 2009 2:10pm

Major updates to Spy

We've just released some great updates to Spy. We made a 5 minute screencast explaining everything, so watch that, or read more below the video embed.

The biggest thing you'll notice is the new map, which adds a lot of life to Spy. Each dot on the map represents a visitor on your site right now. As visitors come and go, dots will appear and disappear.

When a visitor performs an action, a popup box will display next to them on the map with more details. The map can be zoomed in for greater detail, and the popup boxes will also have more detail in this view.

The popular data box is now on its own as well, instead of being part of the action stream. Previously you had to choose between seeing the actions or seeing the popular data. Now you can see both at once.

If you click any item in this box, it will apply a filter that applies to everything Spy, including the map. However, one change we made is that the visitors online figure at the top now always displays the global value, rather than relative to the visitors. We thought it was neat to have that value relative to the filters but it caused too much confusion, particularly when a filter for just one visitor was applied.

The visitors sidebar at the bottom now includes the time they arrived by default (before you could only see it when you zoomed in on them), and it also shows you how many actions they have performed so far, which helps find the most active visitors, which are typically more interesting to watch.

Let us know what you think!
25 comments |   Jul 20 2009 4:25pm

Recent updates to Clicky

We've silently been releasing new features and bug fixes over the last 2 weeks. I wanted to let everyone know about the recent changes, because it's doubtful anyone has noticed all of them.


  • Official support for goals with outbound links or sub-domains

  • "More than once" goals. By default, any goal can only be completed once per visitor. For certain types of goals, this may not make sense. Now there is an option to allow a goal to be completed as many times as it actually happens for an individual visitor.

  • The main goals page can now be sorted by column (e.g. sort by best conversion rate, most revenue, etc)

  • Goal and revenue data now show up in "the basics" dashboard module, as well as when filtering visitors

Visitor filtering

  • More data available up front. When applying filters, we've always shown you summary data (the exact same data that's in "the basics"), but to get more data you had to choose a drop down menu to view e.g. top countries for the filtered visitors. Now we show you top referring domains and top searches by default, as we feel that's the most important data to most people, but you can choose from a menu to view other data as well. We'll add a preference in the future to permanently set which data you want to view by default.

  • "Prettier" additional data. When viewing additional data before, it was a custom coded, fairly generic table. Now it's being sent through the same functions that create most of our graphs so it looks a lot better:

  • Bug fix: Viewing "top links" for filtered visitors now excludes search engine referrers.
  • Bug fix: Comparing one visitor segment to another was broken, now it's fixed.

Other changes

  • We now track daily values for actions types seperately (page views, downloads, outbound links, and clicks). You can see the daily values in "the basics" dashboard module, click the "expand" link. (Note: Since we only track additional action types for paying members, this link will only show up if you have a premium account).

  • Our new homepage is up, including the testimonials you all gave us back in April.

  • There's always been an option to hide ISP's in the visitors list. There's now an option to also hide them in the main hostname/organization pages. This is available in your site preferences.

  • Tracking code now has automatic support for SSL pages. This probably doesn't affect many existing users as they have already installed the secure tracking code if needed, but we receive quite a few emails from people who ask why our tracking code generates warnings in MSIE when used on secure pages. This was always caused by using the "normal" tracking code, instead of the secure version. So, now we've changed it so it works out of the box with both secure and insecure pages without having to worry about using the "right" code, because now there's just one code! If you want to use this, grab a fresh copy of the tracking code from your preferences.

  • Fixed a bug with the API that created some data formatting errors when mixing different types together (e.g. tally and popular data, or visitors-list and popular data, in the same request).

  • API requests for "visitors-list" now include the landing page for each visitor.
4 comments |   Jun 22 2009 7:10pm

Announcing, a new micropayment service from the creators of Clicky

We're just pleased as punch to announce the launch today of, a new micropayment service from the same team behind Clicky Web Analytics.

There have been countless micropayment systems developed over the years, and they all have one thing in common: Serious Fail. They are too complex, both for a site to integrate, and for a visitor to use. But micropayments have become a hot topic again, with many major sites closing down because their sole revenue source - ads - just doesn't pay the bills.

We saw an opportunity here and jumped at it. We already monitor sites with Clicky, and we got to thinking - what if there was a micropayment service that worked the same way? Users pay a monthly fee into the Contenture pool, then we monitor what sites they go to, and automatically distribute each user's money to those sites every month based on how often that user visited the site. This makes for a completely seamless experience, and we believe it's one that will finally bring micropayments to the masses.

So why would a user pay for Contenture? That's where the second feature of Contenture comes in - premium microservices. Contenture lets you offer exclusive features to paying Contenture members, such as removing ads, giving exclusive access to certain content (e.g. /archives/), giving priority access to new content, commenting privileges, and more. All of these features are optional though, which brings up the question: Why would you not install Contenture on your site? By just putting a piece of Javascript on it, you will automatically make money from every paying Contenture user who visits your site, even if you never enable any premium feature.

Every new paying user benefits the entire system. For this reason, Contenture has an affiliate system, like Clicky does. For every new paying user that signs up through your link, we'll pay you $1. But as a special promo to Clicky users, we're going to offer you double that affiliate rate for a full year. When you register, just enter in "clicky" in the promo code section. The first 100 accounts that sign up with this code will get the special affiliate rate.

We plan to integrate Contenture fully into Clicky in the very near future, as an alternative payment method. We will also be disabling ads on Clicky for free accounts that sign up for a paying Contenture account.

So what are you waiting for? Go register for Contenture, and let the Micropayment Revolution begin!
11 comments |   May 26 2009 12:49pm

Do us a favor?

Kaspersky is an anti-virus and "online security" software suite. They have decided that is a malicious tracking service and have blocked access to it for anyone using their software. I have contacted them more than once and they refuse to acknowledge the problem and just send me generic form responses about the steps I need to take if I'm having problems accessing the internet. However, I have been contacted by at least 10 unique users who have pinned down the problem to their software. When they disable it, suddenly they can access Hmm, amazing coincidence, that.

I figure, if they suddenly get a bunch of support requests specifically for problems with, maybe they'll fix it. It doesn't matter if you actually use this product, we're just asking you to submit a support request for this problem. Tell them you use their online security software and when it's enabled you are unable to access, but you can access all of the other sites you regularly visit, and when you disable their software, you can then access

We're very thankful to any of you who are able to help us out with this issue. It is a serious issue, as this product has a fairly large install base.

Consider this a birthday gift from you to me. I turned 31 today and I'm feeling awfully old. :(
32 comments |   May 19 2009 9:48am

Next Page »

Copyright © 2018, Roxr Software Ltd     Blog home   |   Clicky home   |   RSS