During a discussion with Nick we wondered what clean URLs cost in terms of performance. It’s certainly nice to use clean URLs such as /blog/my_post_title instead of numerical URLs such as /blog/123, for ease of remembering, and verbosity. So how to go about testing this?

Test Setup

  • Macbook Pro 2.16Ghz C2D. 2GB RAM.
  • Rails 1.2.3
  • MySQL 5.0.37 (Query Cache turned off)

I started by creating a simple Rails model with only an id and a permalink of the style shown above, created by iterating over the OS X words file. Over a million instances of this were then created in the database. I then randomly selected 10,000 of these to be found by by my tests.

Non-Indexed = Slow!

By timing this using the Rails benchmarker script, and taking away the 6s of setup involved in each test, I got the following times from finding the 10,000 records:

  • find(id) : 12.0s
  • find_by_permalink(permalink) : 4453s (Over an hour)

I didn’t think these results were even worth generating a graph for, the difference is startling. Using the simple permalinks is almost 400 times slower than using the numerical IDs. But all is not lost.

Indexing = Fast

So how can we make this faster? The fastest way to do this is to add an index to the permalink field, you can do this in a Rails migration with the following:



  add_index :samples, :permalink

If you want an index on more than just one field, feed the second attribute an array of field names.

The graph below shows just how close using an index on the permalink gets us. 1.8s extra for our 10,000 finds, with nice URLs rather than numeric IDs. That’s pretty acceptable, especially for a production app that needs to be fast. But can we make it faster?

Configuration Over Convention

The rails find_by_foo methods are nice, but there are faster options. How about manually setting the conditions?



  find( :first, :conditions => [ ":permalink = ?", permalink ] )

Or how about doing all of the SQL manually?



  find_by_sql(["SELECT * FROM samples WHERE permalink = ?", permalink])

So what does this give us? By just setting the :conditions manually, we get down to 12.1s, scratching at the door of the ID based find. And the entirely manual SQL? 10.9s, a whole second faster than our original query. (Obviously we can manually rewrite the find query to use SQL, making it as fast, if not faster)

Conclusion

If you are going to use permalinks or search on some other field of your tables, make sure to use an index. Other speedups could be obtained by writing the SQL yourself, but these are too small to be worth the hassle if your queries become more complicated in the future.

P.S. Loving the WIFI on GNER Trains, posted at over 100mph!