With my latest few projects, I practice on how to improve rails performance. With this blog, I would like to introduce a basic practice to improve query performance in rails by index table column.
What is database indexing?
Indexing is the way that database store data structure by stored and associated with the actual record, which will make records easier and faster to find in the table.
Why need Indexing?
We need to index a column when we think that column will use the most for a query or complex query in a database. That will decrease the query time and increase query performance for a database. Indexing is really important for performance when records grow. Also while a number of request by to server increase, indexing is the key for the site to load faster.
How to index column in rails?
Add below code pattern to your migration file in situation you need
When create new table :
When add new column to table :
When index the existing column in table:
When to index?
Index column performance query the most :
The method search in class Product above will often use to searching for products. So when product records grow to more than thousands. And many users do some search at the same time. So app performance will start to slow down a bit as some query will queue. Surely this name column needs to index to decrease search and queue time for each query.
Index reference column :
To get all product belong to category:
Rails will produce sql query:
As category_id in table products will use for to find products that belong to a category. This column needs to index for better performance each time get products of the category.
Index column that some gem use the most :
For my late project my team use geocoder gem to find near user by lat long of the user
To find nearby user in 25 km we have to do:
Geocoder will produce sql query:
We can see SQL statement above that geocoder use column lat and long for its complex query which will lead this query to be slower when record up to too many to handle. So the better way is to index this lat and long columns that use by this gem.
Bad of indexing
Index table’s column will increase the size of record because it will copy that record for sorting it.