search

Phuks-co / throat

Open Source link aggregator and discussion platform powering Phuks
https://phuks.co
MIT License
74 stars 32 forks source link

Index Sid in SubPost table for fast retrieval for sub filter posts #136

Closed axiomxd closed 4 years ago

axiomxd commented 4 years ago

Why doesn't phuks use any indexing on foreign key field like pid in subcomment table for fast queries/retrieval of data. Thanks

Polsaker commented 4 years ago

We do, but I've forgotten to add a migration for this after we switched from our old .sql dump to migrations for database setup

axiomxd commented 4 years ago

We do, but I've forgotten to add a migration for this after we switched from our old .sql dump to migrations for database setup

Does it index parentcid in SubPostComment table? Does it require to index parentcid of SubPostComment. Comment retrieval is using pid of SubPostComment. So does it need to index parentcid.

Polsaker commented 4 years ago

Yes.

axiomxd commented 4 years ago

Yes.

I am testing in local development. Why is parentcid need to be indexed? parentcid is not used in where clause for retrieval of comments from database. Pid is used for retrieval of comments. So why there is a need for indexing parentcid? I am confused.

Polsaker commented 4 years ago

Just double checked and the migrations are already creating most of the required indexes. I just pushed a migration with three more indexes that shouldn't impact everyday use a lot but should help anyway.

axiomxd commented 4 years ago

Just double checked and the migrations are already creating most of the required indexes. I just pushed a migration with three more indexes that shouldn't impact everyday use a lot but should help anyway.

@migrator.create_model class SubPostComment(pw.Model): cid = pw.CharField(max_length=40, primary_key=True) parentcid = pw.ForeignKeyField(backref='subpostcomment_set', column_name='parentcid', field='cid', model='self', null=True) pid = pw.ForeignKeyField(backref='subpostcomment_set', column_name='pid', field='pid', model=migrator.orm['sub_post'], null=True)

Where is the indexing for pid? Does peewee migration create indexing automatically for foreignkey?

Polsaker commented 4 years ago

Yes