---
title: "Rails 5.2 supports descending indexes for MySQL"
description:
  "Rails 5.2 now supports descending indexes for MySQL for versions 8.0.1 and
  higher."
canonical_url: "https://www.bigbinary.com/blog/rails-5-2-supports-descending-indexes-for-mysql"
markdown_url: "https://www.bigbinary.com/blog/rails-5-2-supports-descending-indexes-for-mysql.md"
---

# Rails 5.2 supports descending indexes for MySQL

Rails 5.2 now supports descending indexes for MySQL for versions 8.0.1 and
higher.

- Author: Chirag Shah
- Published: January 10, 2018
- Categories: Rails 5.2, Rails

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 .

```ruby
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

```ruby
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

```ruby
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](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html#mysqld-8-0-1-optimizer)
for descending indices.

Rails community was quick
[to integrate it as well](https://github.com/rails/rails/pull/28773). 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.

```ruby
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
```

## Links

- [Human page](https://www.bigbinary.com/blog/rails-5-2-supports-descending-indexes-for-mysql)