CODEWITHSUNDEEP
mysqlsqlruby-on-railsactiverecordindexing
Yogzzz
Yogzzz

Reputation: 2785

Database Indexing with Rails app

I have the tables: Products, Sub_Categories and Categories. Categories has many Sub_Categories, Sub_Categories belongs_to Categories and has many products and product belongs to sub_categories. My products table has a sub_category_id; my sub_category table has a category_id. What would be the most efficient way to index these tables to increase the app load time?

Upvotes: 0

Views: 168

Answers (3)

Anil
Anil

Reputation: 3919

Without any more info, for better access times for your queries, you would index these:

categories on id
subcategories on id
subcategories on category_id
product on id
product on sub_category_id

Upvotes: 1

Lucas Wiman
Lucas Wiman

Reputation: 11227

Anil's answer is correct, but as he intimates, your question is too vague. I would suggest turning on query logging, and seeing which queries are taking up time in page loading. Then take the offending queries, and type EXPLAIN {query} into a mysql prompt (which you can reach using rails dbconsole or the standalone mysql client provided by Oracle). Look at the indices used in the bottleneck queries, and ask a more specific question.

I'm guessing that a lot of your queries are querying for products by their category ID. Adding an indexed category_id column to Products might speed things up by avoiding a join, though then the onus on you to keep that column up-to-date.

Upvotes: 0

cdesrosiers
cdesrosiers

Reputation: 8892

You might also use eager loading. For example, if you are going to need a category and several subcategories, load everything in fewer db queries with 

Category.includes(:sub_categories).find(:id)

. See http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations.

Upvotes: 1

Related Questions