Performance

Recently I was working on a couple reports that were running slow. I was looking for indexes that could be created or modified in order to speed them up and provide a quicker response on my SellersToolbox.com website. I was able to find a couple but then the issue of dates popup up. I provide monthly, quarterly, and yearly reports and trying to select the right dates is very important. I have some logic there that determines the right range to be put into my queries.

What I learned recently though is that using functions such as YEAR(), MONTH(), and DAY() defeat the use of indexes and primary keys most of the time. Because these functions have to be performed on the date value, each record in the table is usually processed. This slows the query down greatly and causes a lot of needless computation to result.

What I found was the BETWEEN option in MySQL. Using this allows the query optimizer to utilize indexes fully, and eliminates the need for many date computations which can be expensive.

An added benefit I found was how to handle the beginning and end dates for my queries. Instead of calculating what the end date was, I could use the INTERVAL term and let MySQL figure it out for me. This also allowed me to use the same query for my monthly, quarterly, and yearly time ranges very simply. All I had to do was take the date and the interval such as the 3 examples below.

    BETWEEN '2011-10-01' AND '2011-10-01' + INTERVAL 1 MONTH
    BETWEEN '2011-10-01' AND '2011-10-01' + INTERVAL 3 MONTH
    BETWEEN '2011-10-01' AND '2011-10-01' + INTERVAL 1 YEAR

In this way I didn't have to do any extra work in my application, and I was assured that MySQL would choose records in the correct date range and take advantage of my indexes and primary keys. In this case, trying to do some performance improvements taught me a better way to write my SQL and resulted in less code which was easier to understand.

As you can see, modifying the query with the right amount of time is quite trivial. I have dropdown values in the SELECT on my web pages and all I have to do is append "-01" and then append the interval the user has chosen. Its great when code can be made simpler and more powerful at the same time.