Midnight publishing of objects & MySQL Query Cache

Usually we have to list objects that are published or expired on some day or time, for e.g. in a shop catalog, say you want to make a product available on a particular date and your conditions may look like the following in a Rails model:

  named_scope :available, lambda{
    {
      :conditions=>["products.is_available = 1 and products.available_at <= ?", Time.now.to_s(:db)]
    }
  }

MySQL has a facility to cache the results of queries and respond quickly when asked again. To make use of it, the query should be constant. In the above, the Time.now changing every second, you end up asking MySQL with different query every second. In reality, many listings can work without being too sensitive to time. You can publish at midnight without doing much harm. A simple change to Date.today.at_midnight or even the immediate hour can result in great performance of your queries:

  named_scope :available, lambda{
    {
      :conditions=>["products.is_available = 1 and products.available_at <= ?", Date.today.at_midnight.to_s(:db)]
    }
  }

2 Comments »

  1. Duncan Beevers said,

    August 11, 2008 @ 12:37 am

    If you’re always going to quantize your condition to a single day, you should probably be using a date column and change available_at to available_on (to fit with the Rails convention of created_on)

    Additionally, changing the column to a date guarantees better grouping for any index that includes the available_on column.

    It sounds like an index on [ 'is_available', 'available_on' ] would serve this query well.

  2. blj said,

    August 11, 2008 @ 1:01 am

    Duncan, Thank you for your comment.

    For some cases, the datetime type can be useful, say if the application can only tolerate an hour or 10 minutes late of the time originally it was set to be published. I was only trying to emphasise on why it is a bad idea to change the query so often as every second.

    However, I agree with the index and changing the column type to date — good tip. It all depends on their need. Thanks again.

RSS feed for comments on this post · TrackBack URI

Leave a Comment