Unit tests is very slow on MySQL 5.1 or later

[Jun Omae]

If database backend is MySQL 5.1 or later, unit tests is very slow.

reset_mysql_db in trac/test.py executes TRUNCATE TABLE for tearDown of each test. This TRUNCATE TABLE is very slow on MySQL 5.1 or later. We use TRUNCATE TABLE because we need to reset auto_increment value of tables.

It would be fast to use DELETE FROM for table with no auto_increment rather than TRUNCATE TABLE.

trac/test.py

@@ -205 +205 @@
     dbname = os.path.basename(db_prop['path'])
     if dbname:
         cursor = db.cursor()
-        cursor.execute('SELECT table_name FROM information_schema.tables '
+        cursor.execute('SELECT table_name, auto_increment '
+                       'FROM information_schema.tables '
                        'WHERE table_schema=%s', (dbname,))
         tables = cursor.fetchall()
-        for table in tables:
-            # TRUNCATE TABLE is prefered to DELETE FROM, as we need to reset
-            # the auto_increment in MySQL.
-            cursor.execute('TRUNCATE TABLE %s' % table)
+        for table, auto_increment in tables:
+            if auto_increment is None or auto_increment == 1:
+                # If auto_increment is not used or it is 1, we use DELETE FROM
+                # as we need not reset.
+                cursor.execute('DELETE FROM %s' % table)
+            else:
+                # TRUNCATE TABLE is prefered to DELETE FROM, as we need to
+                # reset the auto_increment in MySQL.
+                cursor.execute('TRUNCATE TABLE %s' % table)
         db.commit()
         return tables
 

Results;

	Before	After
0.12-stable with MySQL 5.0.95	113.387s	85.090s
0.12-stable with MySQL 5.1.71	1359.702s	323.143s
0.12-stable with MySQL 5.5.35	788.180s	253.629s