← Back to Upcase

SQL order index

(Thiago Gabriel B. da Silva) #1
  1. How do I know if I’m taking advantage of SQL index on my queries?

For example for this migration:

def change
  add_index :colors, :name, order: :name
end

And then I run
Color.order(name: :asc)

  1. Does it use index by default?

  2. Is the index ignored when I run Color.all ?

I didn’t find many content about Rails + SQL Index order on the internet.

(Rowland Lip) #2

It depends on the database but indexes are usually used automagicly by the database when they may speed up a query.

In mysql you can use explain to get some info about how your query will execute see http://dev.mysql.com/doc/refman/4.1/en/using-explain.html, and there is something similar in postgres.

If you need to get at the SQL that rails is going to run try the to_sql method and then you can do the explain in your db console.

i.e. Color.order(name: :asc).to_sql or Color.all.to_sql