miguelszerman
In the context guide on Phoenix the following migration file is created for PostgresSQL:
defmodule Hello.Repo.Migrations.CreateProductCategories do
use Ecto.Migration
def change do
create table(:product_categories, primary_key: false) do
add :product_id, references(:products, on_delete: :delete_all)
add :category_id, references(:categories, on_delete: :delete_all)
end
create index(:product_categories, [:product_id])
create index(:product_categories, [:category_id])
create unique_index(:product_categories, [:product_id, :category_id])
end
end
Multicolumn indexes can also perform queries that involve only some of the columns, and thus the create index(...) for :product_id and :category_id isn’t needed since it’s included in the create unique_index(:product_categories, [:product_id, :category_id]).
What do you think?
Where Next?
Trending in Discussions
Other Trending Topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
At WyeWorks, we believe great software is built by combining technical excellence with genuine human connection
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #library
- #deployment
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #elixirconf
- #channels
- #exunit
- #discussion
- #code-sync
- #podcasts
- #javascript
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixirconf-us
- #blog-post
- #ai
- #elixir-ls
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #hex
- #security
- #metaprogramming
Our Sponsors
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Elixir, Erlang & BEAM conferences from Code Sync - join us in 2026!
Master modern tools, write cleaner code, and tackle real-world challenges.
Producing high quality Elixir screencasts since 2017.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
Showing Posts 1 to 6- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
zachallaun
This does not seem to be the case for MySQL, see the notes towards the end of these docs: https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html
Are there any instances in the generators where they generate different code depending on what database driver you’re using? If not, this doesn’t seem like a big enough win to add it, IMO.
miguelszerman
Yep, I was considering the generator only for PostgresSQL. Thx!
I agree that the overhead of the two individuals
create index(...)is pre-optimization. I would remove it from the guide since it adds complexity and can confuse beginners like me.joey_the_snake
The index for
category_idis still needed. Using[:product_id, :category_id]to search forcategory_idwhenproduct_idis unrestricted is not efficient.edit: to clarify, I’m talking about regular btree indices. the traversal is not efficient in that case.
miguelszerman
What does unrestricted mean?
ChrisYammine
The indexes on the individual columns are not redundant. Here’s an excerpt from the page you linked that explains why they are not equivalent:
Creating the individual column indexes is the right thing to do so that the query planner behaves in the “least surprising” way
joey_the_snake
If you want to find specific category ids without limiting the values of product id.