Repository navigation
Fix compound unique index for template in hierarchies_table #410
Description
This is a proposal for the hierarchies_table template.
https://github.com/ClosureTree/closure_tree/blob/8550ddab5671a0d3b002e954796de44826585728/lib/generators/closure_tree/templates/create_hierarchies_table.rb.erb
I feel that the unique compound index of ancestor_id and descendant_id, is needed and generations is redundant.
However, considering performance, I think it's necessary to define a new compound index for ancestor_id, descendant_id, and generations.
add_index :<%= migration_name %>, [:ancestor_id, :descendant_id],
unique: true,
name: "<%= file_name %>_anc_desc_uniq_idx"
add_index :<%= migration_name %>, [:ancestor_id, :descendant_id, :generations],
name: "<%= file_name %>_anc_desc_gene_idx"
Activity
seuros commentedon Jan 2, 2023
For example, suppose we have a closure hierarchy table with the following rows:
In this case, the (ancestor_id, descendant_id) combination of (1, 3) appears twice, but the rows can be distinguished by the generations column - one row represents a direct ancestor-descendant relationship (with generations equal to 1), while the other represents an indirect ancestor-descendant relationship (with generations equal to 2).
By including generations in the index, the database can more quickly retrieve rows with a specific ancestor_id and descendant_id combination, as it narrows down the search by also considering the generations value. This can be especially useful when querying the table for ancestry relationships at a specific generation depth.
fkmy commentedon Jan 3, 2023
The example table provided appears to show the (ancestor_id, descendant_id) combination of (1, 3) only once.
Is the content of the table correct?
I understand an index for the (ancestor_id, descendant_id, generations) combination is needed.
I think it would be good to create an index for the (ancestor_id, descendant_id, generations) combination separately from the unique index.
seuros commentedon Jan 3, 2023
Sure. You want to open a pr?
fkmy commentedon Jan 4, 2023
I will try making a pr. Thank you.