[rails] improving the performance of a query involving date-ranges

I have an audit-log table with +300M rows and while most messages are just confirming we performed some very repetitive action, I was thinking of an easy way to compress the data: when a month is over, instead of adding 1000 lines per day with the message "CHECKED IT", replace them with 1 line saying we "CHECKED IT [logged X times on day Y]". Wrote the code, and that was pretty easy. But then came the moment I had to run this, and the simplest query to check the number of messages:

select count(*) as count
from audit_logs
where created_at < '2016-01-01' 
  and created_at > '2015-12-01'       
  and message = 'Checking for new map-requests'  
  and organization_id = 3

took 15 seconds. So then I checked my indexes: I had only an index on organization_id.

I read an article on the best way to create an index when searching for ranges, and it suggested a combined index on the equality operator first, and then the range second, so in my case I added a migration with the following addition

add_index :audit_logs, [:organization_id, :created_at]

Rerunning the query showed an immense improvement, and now the query only took a mere 20ms :) :) :)


Comments
Add comment

Recent comments

Tags

ruby on rails 34 ruby 26 rails3 17 rails 15 oracle 11 rspec 9 rspec2 7 jquery 7 ubuntu 5 javascript 5 windows 5 activerecord 3 refactoring 3 geoserver 3 gis 3 arrrrcamp 3 actionmailer 2 oracle spatial 2 tdd 2 postgis 2 routing 2 rvm 2 mongoid 2 csharp 2 thin 2 win32 2 gem 2 rails4 2 git 2 service 2 haml 2 cucumber 2 view testing 2 i18n 1 displaysleep 1 spatial 1 gemsets 1 wubi 1 oracle_enhanced_adapter 1 migrations 1 watchr 1 ci 1 plugins 1 coderetreat 1 ie8 1 ssl 1 oci 1 nested model form 1 wcf 1 11.04 1 jsonp 1 ruby-oci8 1 teamcity 1 engines 1 pgadmin 1 soap 1 content_for 1 word automation 1 plugin 1 capybara 1 xml 1 bootstrap 1 migrate to rails3 1 mvc 1 unity 1 rendering 1 word2007 1 x64 1 limited stock 1 fast tests 1 pl/sql 1 delayed_job 1 pdf 1 test coverage 1 optimization 1 processing 1 borland 1 method_missing 1 cross-browser 1 devise 1 schema_plus 1 mongo 1 mongrel 1 dual boot 1 usability 1 mongrel_service 1 dba 1 mission statement 1 model 1 metadata 1 rcov 1 exceptions 1 image_tag 1 attachments 1 bde 1 css 1 yield 1 ajax 1 generative art 1 rails-assets 1 coordinate systems 1 submodules 1 netzke 1 ora-01031 1 authlogic 1 postgresql 1 shopping cart 1 agile 1 fast_tagger 1 subjective 1 wice_grid 1 generators 1 nvidia 1 mongodb 1 etsyhacks 1 staleobjecterror 1 session 1 jeweler 1 wordpress hacked 1 jasmine 1 heroku 1 rjs 1 life 1 unobtrusive-javascript 1 render_anywhere 1 html5 1 rails31 1 json 1 cocoon 1 mingw32 1 observe_field 1 osx 1 actionwebservice 1 testing 1 debugging 1 strings 1