StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>Should I add "index" to all the foreign keys like "xxx_id"?</p> </blockquote> <p>It would be better, because it accelerates the search in sorting in this column. And Foreign keys are something searched for a lot.</p> <p>Since Version 5 of rails the index will be created automatically, for more information see <a href="http://guides.rubyonrails.org/v5.0/migrations.html" rel="nofollow noreferrer">here</a>.</p> <blockquote> <p>Should I add "index" to the automatically created "id" column?</p> </blockquote> <p>No, this is already done by rails</p> <blockquote> <p>Should I add "index(unique)" to the automatically created "id" column?</p> </blockquote> <p>No, same as above</p> <blockquote> <p>If I add index to two foreign keys at once (<code>add_index (:users, [:category_id, :state_id])</code>, what happens? How is this different from adding the index for each key?</p> </blockquote> <p>Then the index is a combined index of the two columns. That doesn't make any sense, unless you want all entries for one <code>category_id</code> <strong>AND</strong> one <code>state_id</code> (It should be <code>category_id</code> not <code>category</code>) at the same time.</p> <p>An Index like this would speed the following request up:</p> <pre><code># rails 2 User.find(:all, :conditions => { :state_id => some_id, :category_id => some_other_id }) # rails 3 User.where(:state_id => some_id, :category_id => some_other_id) </code></pre> <p>Where</p> <pre><code>add_index :users, :category_id add_index :users, :state_id </code></pre> <p>will speed up these requests:</p> <pre><code># rails 2+3 User.find_by_category_id(some_id) User.find_by_state_id(some_other_id) # or # rails 2 User.find(:all, :conditions => {:category_id => some_id}) User.find(:all, :conditions => {:state_id => some_other_id}) # rails 3 User.where(:category_id => some_id) User.where(:state_id => some_other_id) </code></pre> <blockquote> <p>I should add "index with unique" for xxx_id, right?</p> </blockquote> <p>No, because if you do this, only one user can be in one category, but the meaning of category is that you can put more <strong>many</strong> user into one category. In your <code>User</code> model you have something like this <code>belongs_to :category</code> and in your Category model something like <code>has_many :users</code>. If you have a <code>has_many</code> relationship the <code>foreign_key</code> field must not be unique!</p> <p>For more detailed information on this you should take a look at <a href="https://stackoverflow.com/users/87189/tadman">tadman</a>'s great <a href="https://stackoverflow.com/questions/3658859/when-to-add-what-indexes-in-a-table-in-rails/3661096#3661096">answer</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload