Hacker News Reader

Comment by veeti | Hacker News Reader

> Rails does not (cannot) add indexes to your database.

Am I missing something in this sentence? You can add indexes in migrations.

I think the unspoken implication is "for you.". Many newbie Rails devs assume that a line like "has_many :friends" is magic and makes user.friends execute quickly. They won't understand this explanation, but that can plausibly cause a full table scan, or worse, N+2 full table scans if you have something like:

photos_i_am_in = user.friends.map {|f| f.photos.select {|p| p.include? user}}.flatten

(Code somewhat minimalist - don't think too hard about it.)

The #map and #select approach in your example is even worse since it loads all of the associated friend and photo records into memory and tries to use Ruby for queries that should be performed by the database. If there are more than a handful of those records, that process will balloon up and could bring the server to a halt.

Sorry. I meant rails can't do it automatically for you. You will have to figure out based on your queries and add the indexes yourself. Once you have figured out what indexes to add, you can use add_index in your migration file.

Choosing indexes is a world of compromise ; Indexes slow down operations that involve writing. When you have an index that is rarely or never used you'll slow down writing operations on the table for nothing. So you'll have to carefully chose indexes and there's no way any framework could do this for you ... It completely depends of your application and your indexes can change over time.

I think he intended to say that Rails does not (cannot) automatically add indexes to your database, which is true and makes more sense in the context.

Are there any frameworks that do this?

If there are they shouldn't.

As mickeyben says above, choosing indexes is a compromise - you sacrifice performance in one area for performance in another. Only you can make the right decision there, because only you can begin to know how the app will be used in production.

DataMapper's dm-constraints plugin (https://github.com/datamapper/dm-constraints) can automatically add foreign key constraints to your associations, which has the side effect (in MySQL at least) of creating indices on the foreign key columns if they don't already exist.

Yeah, that kind of blew me away too. I posted a comment on the article about it. Even if Rails didn't support it natively you can always execute raw SQL statements in your migrations and just use your DB-specific syntax there to do it.

You are right. That's a pretty glaring hole.

Reply on news.ycombinator.com