# ANN - assert\_efficient\_sql

**URL:** <https://www.ruby-forum.com/t/ann-assert-efficient-sql/125999>\
**Category:** Rails\
**Created:** [January 14, 2008, 3:52pm UTC](https://www.ruby-forum.com/t/ann-assert-efficient-sql/125999 "2008-01-14T15:52:37Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Phlip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ed8c4c/32.png) [@Phlip](https://www.ruby-forum.com/u/Phlip)\
**Post date:** [January 14, 2008, 3:52pm UTC](https://www.ruby-forum.com/t/ann-assert-efficient-sql/125999/1 "2008-01-14T15:52:37Z")

</div>

To ensure your test cases call efficient MySQL…

def test\_my\_case  
assert\_efficient\_sql do  
# just wrap them in this block!  
end  
end

The assertion intercepts and copies out your MySQL SELECT statements,  
then  
calls EXPLAIN on each one, and inspects the results for common problems.

The goal is test cases that resist database pessimization, even as you  
change  
your data relations, to add new features. If you run your tests after  
every few changes, you can easily detect which change broke your  
database’s  
indices and relations.

Blog:

[O'Reilly Media - Technology and Business Training](http://www.oreillynet.com/ruby/blog/2008/01/assert_efficient_sql.html)

RDocs:

[http://efficient-sql.rubyforge.org/](http://efficient-sql.rubyforge.org/)

–  
Phlip

---

<div class="post-metadata">

**Author:** ![Phlip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ed8c4c/32.png) [@Phlip](https://www.ruby-forum.com/u/Phlip)\
**Post date:** [January 14, 2008, 3:53pm UTC](https://www.ruby-forum.com/t/ann-assert-efficient-sql/125999/2 "2008-01-14T15:53:55Z")

</div>

On 14 Jan 2008, at 14:41, Phlip wrote:

> To ensure your test cases call efficient MySQL…
> 
> def test\_my\_case  
> assert\_efficient\_sql do  
> # just wrap them in this block!  
> end  
> end

This looks very interesting! I do have one reservation, which  
basically boils down to ‘the test database is very small’.

For example my customer fixtures define a dozen or so customers  
whereas obviously the production version contains a hell of a lot  
more. So no matter what I do in my code, I’m unlikely to hit  
the :throttle limit. Is it not also the case that mysql might choose a  
different query plan depending on index statistics etc… which are  
likely to be profoundly different in the production (or even  
development) databases ?  
I imagine one could lean on this to produce some sort of continuous  
integration system which would test core ‘routes’ through the system  
for regressions in this area

Fred