server/db: create indexes

5 files changed, 103 insertions, 24 deletions

diff --git a/server/szurubooru/db/comment.py b/server/szurubooru/db/comment.py
index 3367026..a91f1fc 100644
--- a/server/szurubooru/db/comment.py
+++ b/server/szurubooru/db/comment.py
@@ -6,8 +6,10 @@ from szurubooru.db.base import Base
 class CommentScore(Base):
     __tablename__ = 'comment_score'
 
-    comment_id = Column('comment_id', Integer, ForeignKey('comment.id'), primary_key=True)
-    user_id = Column('user_id', Integer, ForeignKey('user.id'), primary_key=True)
+    comment_id = Column(
+        'comment_id', Integer, ForeignKey('comment.id'), primary_key=True)
+    user_id = Column(
+        'user_id', Integer, ForeignKey('user.id'), primary_key=True, index=True)
     time = Column('time', DateTime, nullable=False)
     score = Column('score', Integer, nullable=False)
 
@@ -18,8 +20,10 @@ class Comment(Base):
     __tablename__ = 'comment'
 
     comment_id = Column('id', Integer, primary_key=True)
-    post_id = Column('post_id', Integer, ForeignKey('post.id'), nullable=False)
-    user_id = Column('user_id', Integer, ForeignKey('user.id'))
+    post_id = Column(
+        'post_id', Integer, ForeignKey('post.id'), index=True, nullable=False)
+    user_id = Column(
+        'user_id', Integer, ForeignKey('user.id'), index=True)
     creation_time = Column('creation_time', DateTime, nullable=False)
     last_edit_time = Column('last_edit_time', DateTime)
     text = Column('text', UnicodeText, default=None)
diff --git a/server/szurubooru/db/post.py b/server/szurubooru/db/post.py
index 223cd3d..9ccef28 100644
--- a/server/szurubooru/db/post.py
+++ b/server/szurubooru/db/post.py
@@ -9,8 +9,10 @@ class PostFeature(Base):
     __tablename__ = 'post_feature'
 
     post_feature_id = Column('id', Integer, primary_key=True)
-    post_id = Column('post_id', Integer, ForeignKey('post.id'), nullable=False)
-    user_id = Column('user_id', Integer, ForeignKey('user.id'), nullable=False)
+    post_id = Column(
+        'post_id', Integer, ForeignKey('post.id'), nullable=False, index=True)
+    user_id = Column(
+        'user_id', Integer, ForeignKey('user.id'), nullable=False, index=True)
     time = Column('time', DateTime, nullable=False)
 
     post = relationship('Post')
@@ -19,8 +21,10 @@ class PostFeature(Base):
 class PostScore(Base):
     __tablename__ = 'post_score'
 
-    post_id = Column('post_id', Integer, ForeignKey('post.id'), primary_key=True)
-    user_id = Column('user_id', Integer, ForeignKey('user.id'), primary_key=True)
+    post_id = Column(
+        'post_id', Integer, ForeignKey('post.id'), primary_key=True, index=True)
+    user_id = Column(
+        'user_id', Integer, ForeignKey('user.id'), primary_key=True, index=True)
     time = Column('time', DateTime, nullable=False)
     score = Column('score', Integer, nullable=False)
 
@@ -30,8 +34,10 @@ class PostScore(Base):
 class PostFavorite(Base):
     __tablename__ = 'post_favorite'
 
-    post_id = Column('post_id', Integer, ForeignKey('post.id'), primary_key=True)
-    user_id = Column('user_id', Integer, ForeignKey('user.id'), primary_key=True)
+    post_id = Column(
+        'post_id', Integer, ForeignKey('post.id'), primary_key=True, index=True)
+    user_id = Column(
+    'user_id', Integer, ForeignKey('user.id'), primary_key=True, index=True)
     time = Column('time', DateTime, nullable=False)
 
     post = relationship('Post')
@@ -41,7 +47,8 @@ class PostNote(Base):
     __tablename__ = 'post_note'
 
     post_note_id = Column('id', Integer, primary_key=True)
-    post_id = Column('post_id', Integer, ForeignKey('post.id'), nullable=False)
+    post_id = Column(
+        'post_id', Integer, ForeignKey('post.id'), nullable=False, index=True)
     polygon = Column('polygon', PickleType, nullable=False)
     text = Column('text', UnicodeText, nullable=False)
 
@@ -50,8 +57,10 @@ class PostNote(Base):
 class PostRelation(Base):
     __tablename__ = 'post_relation'
 
-    parent_id = Column('parent_id', Integer, ForeignKey('post.id'), primary_key=True)
-    child_id = Column('child_id', Integer, ForeignKey('post.id'), primary_key=True)
+    parent_id = Column(
+        'parent_id', Integer, ForeignKey('post.id'), primary_key=True, index=True)
+    child_id = Column(
+        'child_id', Integer, ForeignKey('post.id'), primary_key=True, index=True)
 
     def __init__(self, parent_id, child_id):
         self.parent_id = parent_id
@@ -60,8 +69,10 @@ class PostRelation(Base):
 class PostTag(Base):
     __tablename__ = 'post_tag'
 
-    post_id = Column('post_id', Integer, ForeignKey('post.id'), primary_key=True)
-    tag_id = Column('tag_id', Integer, ForeignKey('tag.id'), primary_key=True)
+    post_id = Column(
+        'post_id', Integer, ForeignKey('post.id'), primary_key=True, index=True)
+    tag_id = Column(
+        'tag_id', Integer, ForeignKey('tag.id'), primary_key=True, index=True)
 
     def __init__(self, post_id, tag_id):
         self.post_id = post_id
@@ -84,7 +95,7 @@ class Post(Base):
 
     # basic meta
     post_id = Column('id', Integer, primary_key=True)
-    user_id = Column('user_id', Integer, ForeignKey('user.id'))
+    user_id = Column('user_id', Integer, ForeignKey('user.id'), index=True)
     creation_time = Column('creation_time', DateTime, nullable=False)
     last_edit_time = Column('last_edit_time', DateTime)
     safety = Column('safety', Unicode(32), nullable=False)
diff --git a/server/szurubooru/db/snapshot.py b/server/szurubooru/db/snapshot.py
index 276f914..8fd237e 100644
--- a/server/szurubooru/db/snapshot.py
+++ b/server/szurubooru/db/snapshot.py
@@ -11,8 +11,8 @@ class Snapshot(Base):
 
     snapshot_id = Column('id', Integer, primary_key=True)
     creation_time = Column('creation_time', DateTime, nullable=False)
-    resource_type = Column('resource_type', Unicode(32), nullable=False)
-    resource_id = Column('resource_id', Integer, nullable=False)
+    resource_type = Column('resource_type', Unicode(32), nullable=False, index=True)
+    resource_id = Column('resource_id', Integer, nullable=False, index=True)
     resource_repr = Column('resource_repr', Unicode(64), nullable=False)
     operation = Column('operation', Unicode(16), nullable=False)
     user_id = Column('user_id', Integer, ForeignKey('user.id'))
diff --git a/server/szurubooru/db/tag.py b/server/szurubooru/db/tag.py
index 555e3d7..90cb0e4 100644
--- a/server/szurubooru/db/tag.py
+++ b/server/szurubooru/db/tag.py
@@ -8,9 +8,9 @@ class TagSuggestion(Base):
     __tablename__ = 'tag_suggestion'
 
     parent_id = Column(
-        'parent_id', Integer, ForeignKey('tag.id'), primary_key=True)
+        'parent_id', Integer, ForeignKey('tag.id'), primary_key=True, index=True)
     child_id = Column(
-        'child_id', Integer, ForeignKey('tag.id'), primary_key=True)
+        'child_id', Integer, ForeignKey('tag.id'), primary_key=True, index=True)
 
     def __init__(self, parent_id, child_id):
         self.parent_id = parent_id
@@ -20,9 +20,9 @@ class TagImplication(Base):
     __tablename__ = 'tag_implication'
 
     parent_id = Column(
-        'parent_id', Integer, ForeignKey('tag.id'), primary_key=True)
+        'parent_id', Integer, ForeignKey('tag.id'), primary_key=True, index=True)
     child_id = Column(
-        'child_id', Integer, ForeignKey('tag.id'), primary_key=True)
+        'child_id', Integer, ForeignKey('tag.id'), primary_key=True, index=True)
 
     def __init__(self, parent_id, child_id):
         self.parent_id = parent_id
@@ -32,7 +32,7 @@ class TagName(Base):
     __tablename__ = 'tag_name'
 
     tag_name_id = Column('tag_name_id', Integer, primary_key=True)
-    tag_id = Column('tag_id', Integer, ForeignKey('tag.id'), nullable=False)
+    tag_id = Column('tag_id', Integer, ForeignKey('tag.id'), nullable=False, index=True)
     name = Column('name', Unicode(64), nullable=False, unique=True)
 
     def __init__(self, name):
@@ -43,7 +43,7 @@ class Tag(Base):
 
     tag_id = Column('id', Integer, primary_key=True)
     category_id = Column(
-        'category_id', Integer, ForeignKey('tag_category.id'), nullable=False)
+        'category_id', Integer, ForeignKey('tag_category.id'), nullable=False, index=True)
     creation_time = Column('creation_time', DateTime, nullable=False)
     last_edit_time = Column('last_edit_time', DateTime)
 
diff --git a/server/szurubooru/migrations/versions/49ab4e1139ef_create_indexes.py b/server/szurubooru/migrations/versions/49ab4e1139ef_create_indexes.py
new file mode 100644
index 0000000..e30ccb9
--- /dev/null
+++ b/server/szurubooru/migrations/versions/49ab4e1139ef_create_indexes.py
@@ -0,0 +1,64 @@
+'''
+Create indexes
+
+Revision ID: 49ab4e1139ef
+Created at: 2016-05-09 09:38:28.078936
+'''
+
+import sqlalchemy as sa
+from alembic import op
+
+revision = '49ab4e1139ef'
+down_revision = '23abaf4a0a4b'
+branch_labels = None
+depends_on = None
+
+def upgrade():
+    op.create_index(op.f('ix_comment_post_id'), 'comment', ['post_id'], unique=False)
+    op.create_index(op.f('ix_comment_user_id'), 'comment', ['user_id'], unique=False)
+    op.create_index(op.f('ix_comment_score_user_id'), 'comment_score', ['user_id'], unique=False)
+    op.create_index(op.f('ix_post_user_id'), 'post', ['user_id'], unique=False)
+    op.create_index(op.f('ix_post_favorite_post_id'), 'post_favorite', ['post_id'], unique=False)
+    op.create_index(op.f('ix_post_favorite_user_id'), 'post_favorite', ['user_id'], unique=False)
+    op.create_index(op.f('ix_post_feature_post_id'), 'post_feature', ['post_id'], unique=False)
+    op.create_index(op.f('ix_post_feature_user_id'), 'post_feature', ['user_id'], unique=False)
+    op.create_index(op.f('ix_post_note_post_id'), 'post_note', ['post_id'], unique=False)
+    op.create_index(op.f('ix_post_relation_child_id'), 'post_relation', ['child_id'], unique=False)
+    op.create_index(op.f('ix_post_relation_parent_id'), 'post_relation', ['parent_id'], unique=False)
+    op.create_index(op.f('ix_post_score_post_id'), 'post_score', ['post_id'], unique=False)
+    op.create_index(op.f('ix_post_score_user_id'), 'post_score', ['user_id'], unique=False)
+    op.create_index(op.f('ix_post_tag_post_id'), 'post_tag', ['post_id'], unique=False)
+    op.create_index(op.f('ix_post_tag_tag_id'), 'post_tag', ['tag_id'], unique=False)
+    op.create_index(op.f('ix_snapshot_resource_id'), 'snapshot', ['resource_id'], unique=False)
+    op.create_index(op.f('ix_snapshot_resource_type'), 'snapshot', ['resource_type'], unique=False)
+    op.create_index(op.f('ix_tag_category_id'), 'tag', ['category_id'], unique=False)
+    op.create_index(op.f('ix_tag_implication_child_id'), 'tag_implication', ['child_id'], unique=False)
+    op.create_index(op.f('ix_tag_implication_parent_id'), 'tag_implication', ['parent_id'], unique=False)
+    op.create_index(op.f('ix_tag_name_tag_id'), 'tag_name', ['tag_id'], unique=False)
+    op.create_index(op.f('ix_tag_suggestion_child_id'), 'tag_suggestion', ['child_id'], unique=False)
+    op.create_index(op.f('ix_tag_suggestion_parent_id'), 'tag_suggestion', ['parent_id'], unique=False)
+
+def downgrade():
+    op.drop_index(op.f('ix_tag_suggestion_parent_id'), table_name='tag_suggestion')
+    op.drop_index(op.f('ix_tag_suggestion_child_id'), table_name='tag_suggestion')
+    op.drop_index(op.f('ix_tag_name_tag_id'), table_name='tag_name')
+    op.drop_index(op.f('ix_tag_implication_parent_id'), table_name='tag_implication')
+    op.drop_index(op.f('ix_tag_implication_child_id'), table_name='tag_implication')
+    op.drop_index(op.f('ix_tag_category_id'), table_name='tag')
+    op.drop_index(op.f('ix_snapshot_resource_type'), table_name='snapshot')
+    op.drop_index(op.f('ix_snapshot_resource_id'), table_name='snapshot')
+    op.drop_index(op.f('ix_post_tag_tag_id'), table_name='post_tag')
+    op.drop_index(op.f('ix_post_tag_post_id'), table_name='post_tag')
+    op.drop_index(op.f('ix_post_score_user_id'), table_name='post_score')
+    op.drop_index(op.f('ix_post_score_post_id'), table_name='post_score')
+    op.drop_index(op.f('ix_post_relation_parent_id'), table_name='post_relation')
+    op.drop_index(op.f('ix_post_relation_child_id'), table_name='post_relation')
+    op.drop_index(op.f('ix_post_note_post_id'), table_name='post_note')
+    op.drop_index(op.f('ix_post_feature_user_id'), table_name='post_feature')
+    op.drop_index(op.f('ix_post_feature_post_id'), table_name='post_feature')
+    op.drop_index(op.f('ix_post_favorite_user_id'), table_name='post_favorite')
+    op.drop_index(op.f('ix_post_favorite_post_id'), table_name='post_favorite')
+    op.drop_index(op.f('ix_post_user_id'), table_name='post')
+    op.drop_index(op.f('ix_comment_score_user_id'), table_name='comment_score')
+    op.drop_index(op.f('ix_comment_user_id'), table_name='comment')
+    op.drop_index(op.f('ix_comment_post_id'), table_name='comment')