Help understanding ecto unique index

Questions / Help
1

I’m following a tutorial on using ecto. The code below is a migration to create the users table.

def change do
    create table(:users) do
      add :username, :string
      add :email, :string

      timestamps()
    end

    create unique_index(:users, [:username, :email])
    create unique_index(:users, [:email, :username])
  end

I understand that indexes are used to speed up lookups. I don’t understand why there are two unique indexes here, they look identical to me apart from the order of the column names. Is the order of the columns significant?

Thanks

2

This has nothing to do with ecto, if you are using postgres as you database then this is a part of multicolon index.

3

The order of the columns in the index can matter when querying on only some of the columns, and that’s true if the index is unique or not.

If you index is defined as being on columns “a, b, c” then that index can be used when querying on “a” or “a, b” or “a, b, c” but not when querying on “b”, “c”, or “b, c”.

Having those indices be unique is pretty weird, as it’s basically saying: emails need to be unique per username and usernames need to be unique per email. I think what you want is:

create unique_index(:users, [:email])
create unique_index(:users, [:username])
create index(:users, [:email, :username])
2 Likes