miguelszerman
Redundant code in Ecto.Migration file - Phoenix Context Guide
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?
Marked As Solved
joey_the_snake
The index for category_id is still needed. Using [:product_id, :category_id] to search for category_id when product_id is unrestricted is not efficient.
edit: to clarify, I’m talking about regular btree indices. the traversal is not efficient in that case.
Also Liked
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:
A multicolumn B-tree index can be used with query conditions that involve any subset of the index’s columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on
(a, b, c)and a query conditionWHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry witha= 5 andb= 42 up through the last entry witha= 5. Index entries withc>= 77 would be skipped, but they’d still have to be scanned through. This index could in principle be used for queries that have constraints onband/orcwith no constraint ona— but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.
Creating the individual column indexes is the right thing to do so that the query planner behaves in the “least surprising” way
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.
Popular in Discussions
Other popular topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Develop your skills with books, videos, and courses.
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
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
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.
Real-time error tracking, performance insights, and observability for devs.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"