Mercurial > hg > ltpdarepo

changeset 172:c690b879ddee

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add index for 'submitted' field in 'objmeta' table to speed up activity view.
author Daniele Nicolodi <daniele@grinta.net>
date Sun, 06 Nov 2011 18:10:57 +0100
parents f1cc11dc09b7
children e2a9b0c3d83e
files src/ltpdarepo/sql/database.sql src/ltpdarepo/upgrade.py
diffstat 2 files changed, 10 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/src/ltpdarepo/sql/database.sql	Sun Nov 06 23:15:25 2011 +0100
+++ b/src/ltpdarepo/sql/database.sql	Sun Nov 06 18:10:57 2011 +0100
@@ -36,7 +36,8 @@
   `vdate` datetime DEFAULT NULL,
   `author` text CHARACTER SET utf8,
   PRIMARY KEY (`obj_id`),
-  FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
+  FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE,
+  INDEX (`submitted`)
 ) ENGINE=InnoDB;
 
 CREATE TABLE `collections` (
--- a/src/ltpdarepo/upgrade.py	Sun Nov 06 23:15:25 2011 +0100
+++ b/src/ltpdarepo/upgrade.py	Sun Nov 06 18:10:57 2011 +0100
@@ -370,6 +370,7 @@
 def upgrade_28_29(conn):
     curs = conn.cursor()
 
+    # support procedure for activity view
     curs.execute("DROP PROCEDURE IF EXISTS makeintervals")
     curs.execute("""
     CREATE PROCEDURE makeintervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
@@ -405,3 +406,10 @@
     until thisdate > enddate
     end repeat;
     END;""")
+
+    # for each registered database
+    curs.execute("SELECT db_name FROM available_dbs")
+    databases = [row[0] for row in curs.fetchall()]
+    for db in databases:
+        # add index on "submitted" column to speed up activity view
+        curs.execute("""ALTER TABLE `%s`.objmeta ADD INDEX (submitted)""" % db)