Rails 5.2 supports descending indexes for MySQL

An index is used to speed up the performance of queries on a database.

Rails allows us to create index on a database column by means of a migration. By default, the sort order for the index is ascending.

But consider the case where we are fetching reports from the database. And while querying the database, we always want to get the latest report. In this case, it is efficient to specify the sort order for the index to be descending.

We can specify the sort order by adding an index to the required column by adding a migration .

add_index :reports, [:user_id, :name], order: { user_id: :asc, name: :desc }

PostgreSQL

If our Rails application is using postgres database, after running the above migration we can verify that the sort order was added in schema.rb

create_table "reports", force: :cascade do |t|
  t.string "name"
  t.integer "user_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["user_id", "name"], name: "index_reports_on_user_id_and_name", order: { name: :desc }
end

Here, the index for name has sort order in descending. Since the default is ascending, the sort order for user_id is not specified in schema.rb.

MySQL < 8.0.1

For MySQL < 8.0.1, running the above migration, would generate the following schema.rb

create_table "reports", force: :cascade do |t|
  t.string "name"
  t.integer "user_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["user_id", "name"], name: "index_reports_on_user_id_and_name"
end

As we can see, although the migration runs successfully, it ignores the sort order and the default ascending order is added.

Rails 5.2 and MySQL > 8.0.1

MySQL 8.0.1 added support for descending indices.

Rails community was quick to integrate it as well. So now in Rails 5.2, we can add descending indexes for MySQL databases.

Running the above migration would lead to the same output in schema.rb file as that of the postgres one.

create_table "reports", force: :cascade do |t|
  t.string "name"
  t.integer "user_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["user_id", "name"], name: "index_reports_on_user_id_and_name", order: { name: :desc }
end