Formed in 2009, the Archive Team (not to be confused with the archive.org Archive-It Team) is a rogue archivist collective dedicated to saving copies of rapidly dying or deleted websites for the sake of history and digital heritage. The group is 100% composed of volunteers and interested parties, and has expanded into a large amount of related projects for saving online and digital history.
Join GitHub today
GitHub is home to over 50 million developers working together to host and review code, manage projects, and build software together.
Sign upGitHub is where the world builds software
Millions of developers and companies build, ship, and maintain their software on GitHub — the largest and most advanced development platform in the world.
Option to auto create foreign key indexes #5042
Comments
|
For 4.0 i'd like to have automatic indexes on all associations. |
|
|
|
This can be easily achieved by simple hook/plugin (code in coffeescript): Sequelize = require 'sequelize'
_ = Sequelize.Utils._
sequelize = new Sequelize('postgres://alek:abcdef@localhost:5434/transaction_test')
sequelize.beforeSync (options) ->
# check each foreign key
for own attrName, attr of @attributes when attr.references
# check if index is not already defined for this FK
indexAlreadyDefined = _.find @options.indexes, (index) ->
index.fields.length is 1 and attrName in index.fields
continue if indexAlreadyDefined
# add index on FK
@options.indexes.push({
fields: [attrName]
})
User = sequelize.define('User')
Tag = sequelize.define('Tag')
Post = sequelize.define('Post')
PostTag = sequelize.define('PostTag')
Post.belongsTo(User)
Post.belongsToMany(Tag, { through: PostTag })
sequelize.sync({ force: true, logging: console.log })
# ...
# CREATE INDEX "posts__user_id" ON "Posts" ("UserId")
# ...
# CREATE INDEX "post_tags__post_id" ON "PostTags" ("PostId")
# CREATE INDEX "post_tags__tag_id" ON "PostTags" ("TagId") |
|
@alekbarszczewski Neat! I think we might still want this in core though. |
|
@mickhansen did this make it into core? I don't see any references to indexing on the Associations page of the v4 documentation and references to indexes on the Model Definition page are for indexing other columns with no references to foreign keys. |
|
Since @papb removed the stale label perhaps there's no need to comment as per the stale bot's recommendation, but in any case I do believe this is still an open issue. |
|
@ajmueller Yeah, it was marked as stale by mistake. |
|
Hi everyone, any news about this issue? |
If you create a foreign key constraint no further index will be auto created by postgres (I'm not sure what other DBs will do, I did not check), since there are a couple of different ways to create a index in that case.
But I'd say the average use case would be improved if sequelize would just add a normal btree index on the foreign key if you pass a option to the global sequelize instance. That way new tables would always benefit from that and you do not always have to think about it.
The performance difference is significant in that case.
So I'd say it would be a nice feature to add.