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
Chat & Discussions>Discussions
Latest on Elixir Forum
Sponsor Spotlight
Deploy Phoenix to your own VPS with PaaS ease: push-to-deploy, zero downtime, auto SSL, managed Postgres. 14-day free trial at potions.io
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #blog-post
- #phoenix_html
- #iex
- #graphql
- #ai
- #genstage
- #elixirconf-us
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
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.
Develop your skills with books, videos, and courses.
Bringing learning, work and compliance together since 1987.
Enabling companies to succeed by building software people love.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
First 6 of 6 Posts
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.