3

I am using Rails and postgres.

I have a couple of models using STI and i was wondering where i should put indexes on the tables and why?

For example lets say i have the following setup:

class Comment < AR; end
class MovieComment < Comment; end
class MagazineComment < Comment; end


# Fake Comment Table
id:integer
title:string
body:text
type:string

Thanks!

Mario Zigliotto
  • 8,315
  • 7
  • 52
  • 71

2 Answers2

10

On the type field, if you want only one of MovieComment or MagazineComment. If you don't do that, you won't need the index here. I'm not sure if AR does use type every time, but just to make sure.

Because the id field is a primary key, an index should already be there.

If you want to query by both type and id make sure you have a combined index.

On the other fields: Depends what you query on, but I suppose you want to retrieve these only.

Reactormonk
  • 21,472
  • 14
  • 74
  • 123
1

In general, you need indices in the columns that you will use when performing queries.

If you do MovieComment.find(10), the database will use the index in the id field that Rails will add automatically for you. Same if you do Comment.find(30): Rails will retrieve the commend with id 30 using the index, then it will read the type column and it will return a MovieComment or a MagazineComment.

If you are going to add a feature to search by title, for instance, you will have to create an index in this column as well. In this case, probably a :fulltext index.

An Index in the type column would make a query like MagazineComment.all faster because it is equivalent to Comment.where(type: 'MagazineComment').all, but it is probably not worth it.

Daniel
  • 4,051
  • 2
  • 28
  • 46
  • 2
    do you mind to elaborate more on why it's not worth to index the `type` column? – shinnc Jan 03 '17 at 04:13
  • 2
    Indexes are helpful for selecting single records (or a really small set of records) from the DB. When your select criteria includes a big percentage of the table (let's say that 20% of the comments are `MovieComment` and 80% are `MagazineComment`), the database will probably decide that it is cheaper to do a full table scan every time. http://stackoverflow.com/questions/33911589/mysql-does-not-always-use-index – Daniel Feb 15 '17 at 01:17
  • 1
    Just want to add that @Daniel is not entirely right (not wrong either). Because indexes are used for filters, joins and sorting as well. In general it *is* a good idea to add an index to `type` when using STI types. "Just add it, unless you can reason why it is not needed" is a better guideline. – berkes Jul 07 '20 at 14:41
  • 1
    You are mostly right @berkes on the fact that indexes are not only used to retrieve rows. But my point is that in the case of STI, if you have two types, the rows may probably be about 50% of each type. In this case, the database will not use the index because a full table scan is cheaper. If OP had 30 types (subclasses) the situation would be different. – Daniel Oct 07 '20 at 00:21