Query Testing
Whatever database you use, whether a conventional RDB or one of the new NoSQL databases, the most important and most frequently violated rule of databases still applies:
NEVER do an un-indexed read!
Or to be more exact: Never do an un-indexed read on any customer-facing query on any table with more than one record.
We all know this rule and we all intend to follow it but it can be difficult to do so consistently in an agile environment with constant changes and frequent deployments. The usual methods for making sure queries are appropriately indexed:
1. Review and analyze indexing schemes before major deployments.
2. Monitoring performance after deployment, either formally with New Relic or informally when customers call to complain about response time.
Looking at this list we can see that the first method is BDUF and the second is releasing un-tested code. Both of those are anathema to agile developers.
So what should we be doing?
We should have tests for our queries just like we have tests for our code.
Look at the code in http://gist.github.com/328822 for an example of query tests using MongoDB and the mongo gem in Ruby. This code uses the explain method of MongoDB to assign a rating from 0 - 100 that indicates not only whether the query used an index but also how efficiently. A little experimentation will show what your minimum rating for passing the test should be but 50 is a reasonable place to start.
Query tests like these will not be fast enough to include in your unit tests that run every time you save but would be ideal for the tests that run when code is checked in. Wherever you run them they will save you from ever releasing un-indexed queries again.
