SQL order index

Ruby on Rails
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.

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 MySQL :: MySQL 8.0 Reference Manual :: 8.8.1 Optimizing Queries with EXPLAIN, 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

1 Like