Indexes in SQL {Using Rails}

Indexing in SQL is a useful tool to make querying database tables much faster and easier. It is ideal for when you plan to have large amounts of data in your table and often need to extract that data by certain column values. For example, when a record requires a different, maybe formatted, ID than what's auto-assigned in the database table, or you want to be able to pull a set of records by its value in a category column, indexing is the perfect tool.

You might be thinking, "Why not just use the WHERE clause?" The WHERE clause is not ideal because it causes the database engine to look through every single record in the table. This can result in a substantial amount of search time, especially if you have a table with, say, a million records (which is not hard to have). Why waste that kind of time when you can reduce it to one second with this simple step?

To add an index to a column via Rails Migration:

When first creating your table:

create_table :table_name do |t|
  t.integer :column_name_one, index: true
  t.string :column_name_two, index: true
end

If your column already exists:

add_index :table_name, :column_name

What's happening under the hood when you create indices is: a new table gets created to store each row's value of the indexed column and a pointer to its location in the database. Although it may seem like a questionable trade-off between storage and search time, the new table is considerably small in width since it only stores the index and pointer and not the records themselves. Then, when you use an index in your query, the database engine is able to use the newly created table to quickly obtain those records, instead of searching through every record one-by-one. As one might say, the benefits outweigh the cost.

There are more ways to modify an index to benefit your querying further, which are greatly outlined here. So be sure to check them out when you start implementing indexes in your tables. Hope this helped!