StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Indexing can be a tricky, subtle thing, but there are general rules that apply that can make determining which to use a lot easier.</p> <p>The first thing to remember is that indexes can work in more than one way. An index on A, B, C also works for A, B and simply A, so you can design your indexes to be more versatile if you order them correctly. The phone book is indexed on Last Name, First Name, so you can look up people easily by their last name, or a combination of last name and first name. You cannot, however, look them up directly by their first name. You'd need a separate index for that. The same goes for phone number, which you would have to index as well.</p> <p>With that in mind, there are many things that will dictate how you create indexes:</p> <ul> <li>If you have a <code>belongs_to</code>-<code>has_many</code> relationship pairing, you need to have an index on the foreign key used.</li> <li>If you order your records, and there is a large number of them that will be paginated, you should add that order column to the end of the index.</li> <li>If you have a <code>has_many :through</code> relationship, your join table should have a unique index on both properties involved in the join as a compound key.</li> <li>If you fetch a record directly using a unique identifier such as username or email, that should be a unique index.</li> <li>If you fetch sets of records from a <code>has_many</code> relationship using a scope, make sure there's an index that includes the <code>has_many</code> foreign key and the scope column in that order.</li> </ul> <p>The goal with indexes is to eliminate the dreaded "table scan" or "file sort" operations that occur when your data is not indexed properly.</p> <p>In simple terms, look at the queries being generated by your application and ensure that columns referenced in <code>WHERE</code> or <code>HAVING</code> conditions and <code>ORDER BY</code> clauses are represented in that order.</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