I don't know much about databases, especially compared to my peers. What I do know I've picked up by reading other people's experiences right after I run into a problem. This time I was having a lookup time performance issues. Consider this Rails ActiveRecord query:
That produces this SQL statement:
SELECT "statuses"."text" FROM "statuses" WHERE "statuses"."liked" = 'f'
This query, on my development database, took
107.6ms. I have to do this operation about a 100,000 times! I knew that I could improve the performance with indexes, but I didn't know quite how. My general solution to indexing has been to add one to any field that is: a short string, a boolean field, an integer, or a float. I know this isn't the best, but it's better than no indexes. I stumbled upon this article by Heroku.
My next step was to look for how to do where clauses on indexes in migrations, but I found nothing. This is likely due to the where clause being specific to postgresql. So I went ahead and used execute. Much to my surprise the
db/schema.rb had my index and used a
where: option! This meant ActiveRecord supported it and I could use it freely, so I generated this index:
class AddDislikedIndexToStatuses < ActiveRecord::Migration def change add_index :statuses, :liked, name: "index_statuses_on_disliked", where: "(liked IS FALSE)" end end
And now the query ran in