- Patch #164532 by catch, pwolanin, David Strauss, et al.: improve table indicies for common queries.

16 files changed, 281 insertions, 96 deletions

diff --git a/modules/aggregator/aggregator.install b/modules/aggregator/aggregator.install
index b49a4c96b..73d2c273c 100644
--- a/modules/aggregator/aggregator.install
+++ b/modules/aggregator/aggregator.install
@@ -75,7 +75,8 @@ function aggregator_schema() {
         'description' => t('The {aggregator_category}.cid to which the feed is being assigned.'),
       )
     ),
-    'primary key' => array('fid', 'cid'),
+    'primary key' => array('cid', 'fid'),
+    'indexes' => array('fid' => array('fid')),
   );
 
   $schema['aggregator_category_item'] = array(
@@ -94,7 +95,8 @@ function aggregator_schema() {
         'description' => t('The {aggregator_category}.cid to which the feed item is being assigned.'),
       )
     ),
-    'primary key' => array('iid', 'cid'),
+    'primary key' => array('cid', 'iid'),
+    'indexes' => array('iid' => array('iid')),
   );
 
   $schema['aggregator_feed'] = array(
@@ -171,11 +173,11 @@ function aggregator_schema() {
         'description' => t("Number of items to display in the feed's block."),
       )
     ),
+    'primary key' => array('fid'),
     'unique keys' => array(
       'url'  => array('url'),
-      'title' => array('title')
+      'title' => array('title'),
     ),
-    'primary key' => array('fid'),
   );
 
   $schema['aggregator_item'] = array(
@@ -231,8 +233,8 @@ function aggregator_schema() {
         'description' => t('Unique identifier for the feed item.'),
       )
     ),
-    'indexes' => array('fid' => array('fid')),
     'primary key' => array('iid'),
+    'indexes' => array('fid' => array('fid')),
   );
 
   return $schema;
diff --git a/modules/block/block.install b/modules/block/block.install
index e4fc5e6dc..737f32dd4 100644
--- a/modules/block/block.install
+++ b/modules/block/block.install
@@ -29,7 +29,7 @@ function block_schema() {
       ),
       'theme' => array(
         'type' => 'varchar',
-        'length' => 255,
+        'length' => 64,
         'not null' => TRUE,
         'default' => '',
         'description' => t('The theme under which the block settings apply.'),
@@ -97,6 +97,12 @@ function block_schema() {
       ),
     ),
     'primary key' => array('bid'),
+    'unique keys' => array(
+      'tmd' => array('theme', 'module', 'delta'),
+    ),
+    'indexes' => array(
+      'list' => array('theme', 'status', 'region', 'weight', 'module'),
+    ),
   );
 
   $schema['blocks_roles'] = array(
@@ -126,6 +132,9 @@ function block_schema() {
       'delta',
       'rid'
     ),
+    'indexes' => array(
+      'rid' => array('rid'),
+    ),
   );
 
   $schema['boxes'] = array(
diff --git a/modules/book/book.install b/modules/book/book.install
index 445a20a4a..72a9df86c 100644
--- a/modules/book/book.install
+++ b/modules/book/book.install
@@ -68,11 +68,13 @@ function book_update_6000() {
         'nid'     => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),
         'bid' => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),
       ),
+      'primary key' => array('mlid'),
+      'unique keys' => array(
+        'nid' => array('nid'),
+      ),
       'indexes' => array(
-        'nid'     => array('nid'),
-        'bid' => array('bid')
+        'bid' => array('bid'),
       ),
-      'primary key' => array('mlid'),
     );
     // Add the node type.
     _book_install_type_create();
@@ -273,11 +275,13 @@ function book_schema() {
         'description' => t("The book ID is the {book}.nid of the top-level page."),
       ),
     ),
+    'primary key' => array('mlid'),
+    'unique keys' => array(
+      'nid' => array('nid'),
+    ),
     'indexes' => array(
-      'nid'     => array('nid'),
-      'bid' => array('bid')
+      'bid' => array('bid'),
     ),
-    'primary key' => array('mlid'),
   );
 
   return $schema;
diff --git a/modules/comment/comment.install b/modules/comment/comment.install
index 0cfc026db..d944f3b71 100644
--- a/modules/comment/comment.install
+++ b/modules/comment/comment.install
@@ -156,7 +156,7 @@ function comment_schema() {
     ),
     'indexes' => array(
       'nid'    => array('nid'),
-      'status' => array('status')
+      'status' => array('status'), // This index is probably unused
     ),
     'primary key' => array('cid'),
   );
@@ -197,8 +197,10 @@ function comment_schema() {
         'description' => t('The total number of comments on this node.'),
       ),
     ),
-    'indexes' => array('node_comment_timestamp' => array('last_comment_timestamp')),
     'primary key' => array('nid'),
+    'indexes' => array(
+      'node_comment_timestamp' => array('last_comment_timestamp')
+    ),
   );
 
   return $schema;
diff --git a/modules/contact/contact.install b/modules/contact/contact.install
index 523badcfe..c3ee3c8c9 100644
--- a/modules/contact/contact.install
+++ b/modules/contact/contact.install
@@ -68,10 +68,14 @@ function contact_schema() {
         'description' => t('Flag to indicate whether or not category is selected by default. (1 = Yes, 0 = No)'),
       ),
     ),
-    'unique keys' => array('category' => array('category')),
     'primary key' => array('cid'),
+    'unique keys' => array(
+      'category' => array('category'),
+    ),
+    'indexes' => array(
+      'list' => array('weight', 'category'),
+    ),
   );
 
   return $schema;
 }
-
diff --git a/modules/filter/filter.install b/modules/filter/filter.install
index 3a446f76b..fb69d5127 100644
--- a/modules/filter/filter.install
+++ b/modules/filter/filter.install
@@ -42,7 +42,12 @@ function filter_schema() {
       )
     ),
     'primary key' => array('fid'),
-    'indexes' => array('weight' => array('weight')),
+    'unique keys' => array(
+      'fmd' => array('format', 'module', 'delta'),
+    ),
+    'indexes' => array(
+      'list' => array('format', 'weight', 'module', 'delta'),
+    ),
   );
   $schema['filter_formats'] = array(
     'description' => t('Stores input formats: custom groupings of filters, such as Filtered HTML.'),
@@ -64,7 +69,7 @@ function filter_schema() {
         'length' => 255,
         'not null' => TRUE,
         'default' => '',
-        'description' => t('A comma-separated string of roles; references {role}.rid.'),
+        'description' => t('A comma-separated string of roles; references {role}.rid.'), // This is bad since you can't use joins, nor index.
       ),
       'cache' => array(
         'type' => 'int',
@@ -74,8 +79,8 @@ function filter_schema() {
         'description' => t('Flag to indicate whether format is cachable. (1 = cachable, 0 = not cachable)'),
       ),
     ),
-    'unique keys' => array('name' => array('name')),
     'primary key' => array('format'),
+    'unique keys' => array('name' => array('name')),
   );
 
   $schema['cache_filter'] = drupal_get_schema_unprocessed('system', 'cache');
diff --git a/modules/filter/filter.module b/modules/filter/filter.module
index 4cb5eecc4..e6ed74d6f 100644
--- a/modules/filter/filter.module
+++ b/modules/filter/filter.module
@@ -369,7 +369,7 @@ function filter_list_format($format) {
 
   if (!isset($filters[$format])) {
     $filters[$format] = array();
-    $result = db_query("SELECT * FROM {filters} WHERE format = %d ORDER BY weight ASC", $format);
+    $result = db_query("SELECT * FROM {filters} WHERE format = %d ORDER BY weight, module, delta", $format);
     while ($filter = db_fetch_object($result)) {
       $list = module_invoke($filter->module, 'filter', 'list');
       if (isset($list) && is_array($list) && isset($list[$filter->delta])) {
diff --git a/modules/locale/locale.install b/modules/locale/locale.install
index 239ff9fd6..fe1d6b135 100644
--- a/modules/locale/locale.install
+++ b/modules/locale/locale.install
@@ -23,41 +23,81 @@ function locale_install() {
 /**
  * {locales_meta} table became {languages}.
  */
-function locale_update_6001() {
+function locale_update_6000() {
   $ret = array();
-  switch ($GLOBALS['db_type']) {
-    case 'mysql':
-    case 'mysqli':
-      $ret[] = update_sql("CREATE TABLE {languages} (
-        language varchar(12) NOT NULL default '',
-        name varchar(64) NOT NULL default '',
-        native varchar(64) NOT NULL default '',
-        direction int NOT NULL default '0',
-        enabled int NOT NULL default '0',
-        plurals int NOT NULL default '0',
-        formula varchar(128) NOT NULL default '',
-        domain varchar(128) NOT NULL default '',
-        prefix varchar(128) NOT NULL default '',
-        weight int NOT NULL default '0',
-        PRIMARY KEY (language)
-      ) /*!40100 DEFAULT CHARACTER SET UTF8 */ ");
-      break;
-    case 'pgsql':
-      $ret[] = update_sql("CREATE TABLE {languages} (
-        language varchar(12) NOT NULL default '',
-        name varchar(64) NOT NULL default '',
-        native varchar(64) NOT NULL default '',
-        direction int NOT NULL default '0',
-        enabled int NOT NULL default '0',
-        plurals int NOT NULL default '0',
-        formula varchar(128) NOT NULL default '',
-        domain varchar(128) NOT NULL default '',
-        prefix varchar(128) NOT NULL default '',
-        weight int NOT NULL default '0',
-        PRIMARY KEY (language)
-      )");
-      break;
-  }
+
+  $schema['languages'] = array(
+    'fields' => array(
+      'language' => array(
+        'type' => 'varchar',
+        'length' => 12,
+        'not null' => TRUE,
+        'default' => '',
+      ),
+      'name' => array(
+        'type' => 'varchar',
+        'length' => 64,
+        'not null' => TRUE,
+        'default' => '',
+      ),
+      'native' => array(
+        'type' => 'varchar',
+        'length' => 64,
+        'not null' => TRUE,
+        'default' => '',
+      ),
+      'direction' => array(
+        'type' => 'int',
+        'not null' => TRUE,
+        'default' => 0,
+      ),
+      'enabled' => array(
+        'type' => 'int',
+        'not null' => TRUE,
+        'default' => 0,
+      ),
+      'plurals' => array(
+        'type' => 'int',
+        'not null' => TRUE,
+        'default' => 0,
+      ),
+      'formula' => array(
+        'type' => 'varchar',
+        'length' => 128,
+        'not null' => TRUE,
+        'default' => '',
+      ),
+      'domain' => array(
+        'type' => 'varchar',
+        'length' => 128,
+        'not null' => TRUE,
+        'default' => '',
+      ),
+      'prefix' => array(
+        'type' => 'varchar',
+        'length' => 128,
+        'not null' => TRUE,
+        'default' => '',
+      ),
+      'weight' => array(
+        'type' => 'int',
+        'not null' => TRUE,
+        'default' => 0,
+      ),
+      'javascript' => array( //Adds a column to store the filename of the JavaScript translation file.
+        'type' => 'varchar',
+        'length' => 32,
+        'not null' => TRUE,
+        'default' => '',
+      ),
+    ),
+    'primary key' => array('language'),
+    'indexes' => array(
+      'list' => array('weight', 'name'),
+    ),
+  );
+
+  db_create_table($ret, 'languages', $schema['languages']);
 
   // Save the languages
   $ret[] = update_sql("INSERT INTO {languages} (language, name, native, direction, enabled, plurals, formula, domain, prefix, weight) SELECT locale, name, name, 0, enabled, plurals, formula, '', locale, 0 FROM {locales_meta}");
@@ -80,7 +120,7 @@ function locale_update_6001() {
  * of error messages from update.php.  All we need to do here is copy
  * locale to language and then drop locale.
  */
-function locale_update_6002() {
+function locale_update_6001() {
   $ret = array();
   $ret[] = update_sql('UPDATE {locales_target} SET language = locale');
   db_drop_field($ret, 'locales_target', 'locale');
@@ -88,18 +128,9 @@ function locale_update_6002() {
 }
 
 /**
- * Adds a column to store the filename of the JavaScript translation file.
- */
-function locale_update_6003() {
-  $ret = array();
-  db_add_field($ret, 'languages', 'javascript', array('type' => 'varchar', 'length' => 32, 'not null' => TRUE, 'default' => ''));
-  return $ret;
-}
-
-/**
  * Remove empty translations, we don't need these anymore.
  */
-function locale_update_6004() {
+function locale_update_6002() {
   $ret = array();
   $ret[] = update_sql("DELETE FROM {locales_target} WHERE translation = ''");
   return $ret;
@@ -108,7 +139,7 @@ function locale_update_6004() {
 /**
  * Prune strings with no translations (will be automatically re-registered if still in use)
  */
-function locale_update_6005() {
+function locale_update_6003() {
   $ret = array();
   $ret[] = update_sql("DELETE FROM {locales_source} WHERE lid NOT IN (SELECT lid FROM {locales_target})");
   return $ret;
@@ -117,7 +148,7 @@ function locale_update_6005() {
 /**
  * Fix remaining inconsistent indexes.
  */
-function locale_update_6006() {
+function locale_update_6004() {
   $ret = array();
   db_add_index($ret, 'locales_target', 'language', array('language'));
 
@@ -233,6 +264,9 @@ function locale_schema() {
       ),
     ),
     'primary key' => array('language'),
+    'indexes' => array(
+      'list' => array('weight', 'name'),
+    ),
   );
 
   $schema['locales_source'] = array(
@@ -272,8 +306,9 @@ function locale_schema() {
       ),
     ),
     'primary key' => array('lid'),
-    'indexes' => array
-        ('source' => array(array('source', 30))),
+    'indexes' => array(
+      'source' => array(array('source', 30)),
+    ),
   );
 
   $schema['locales_target'] = array(
@@ -300,7 +335,7 @@ function locale_schema() {
       ),
       'plid' => array(
         'type' => 'int',
-        'not null' => TRUE,
+        'not null' => TRUE, // This should be NULL for no referenced string, not zero.
         'default' => 0,
         'description' => t('Parent lid (lid of the previous string in the plural chain) in case of plural strings. References {locales_source}.lid.'),
       ),
@@ -311,11 +346,11 @@ function locale_schema() {
         'description' => t('Plural index number in case of plural strings.'),
       ),
     ),
+    'primary key' => array('language', 'lid', 'plural'),
     'indexes' => array(
-      'language'   => array('language'),
-      'lid'    => array('lid'),
-      'plid'   => array('plid'),
-      'plural' => array('plural')
+      'lid'      => array('lid'),
+      'plid'     => array('plid'),
+      'plural'   => array('plural'),
     ),
   );
 
diff --git a/modules/node/node.install b/modules/node/node.install
index 09677a5c4..b22a0e887 100644
--- a/modules/node/node.install
+++ b/modules/node/node.install
@@ -97,13 +97,11 @@ function node_schema() {
       'node_status_type'    => array('status', 'type', 'nid'),
       'node_title_type'     => array('title', array('type', 4)),
       'node_type'           => array(array('type', 4)),
-      'status'              => array('status'),
       'uid'                 => array('uid'),
       'tnid'                => array('tnid'),
       'translate'           => array('translate'),
       ),
     'unique keys' => array(
-      'nid_vid' => array('nid', 'vid'),
       'vid'     => array('vid')
       ),
     'primary key' => array('nid'),
diff --git a/modules/poll/poll.install b/modules/poll/poll.install
index 5998743dc..4859747c1 100644
--- a/modules/poll/poll.install
+++ b/modules/poll/poll.install
@@ -120,10 +120,10 @@ function poll_schema() {
         'description' => t('The IP address this vote is from unless the voter was logged in.'),
         ),
       ),
+    'primary key' => array('nid', 'uid', 'hostname'),
     'indexes' => array(
       'hostname' => array('hostname'),
-      'nid'      => array('nid'),
-      'uid'      => array('uid')
+      'uid'      => array('uid'),
       ),
     );
 
diff --git a/modules/profile/profile.install b/modules/profile/profile.install
index 1082fcb92..f05bccd80 100644
--- a/modules/profile/profile.install
+++ b/modules/profile/profile.install
@@ -119,14 +119,14 @@ function profile_schema() {
       'fid' => array(
         'type' => 'int',
         'unsigned' => TRUE,
-        'not null' => FALSE,
+        'not null' => TRUE,
         'default' => 0,
         'description' => t('The {profile_fields}.fid of the field.'),
       ),
       'uid' => array(
         'type' => 'int',
         'unsigned' => TRUE,
-        'not null' => FALSE,
+        'not null' => TRUE,
         'default' => 0,
         'description' => t('The {users}.uid of the profile user.'),
       ),
@@ -136,9 +136,9 @@ function profile_schema() {
         'description' => t('The value for the field.'),
       ),
     ),
+    'primary key' => array('uid', 'fid'),
     'indexes' => array(
       'fid' => array('fid'),
-      'uid' => array('uid')
     ),
   );
 
diff --git a/modules/statistics/statistics.install b/modules/statistics/statistics.install
index 324616af8..39aa5e48a 100644
--- a/modules/statistics/statistics.install
+++ b/modules/statistics/statistics.install
@@ -109,7 +109,10 @@ function statistics_schema() {
         'description' => t('Timestamp of when the page was visited.'),
       ),
     ),
-    'indexes' => array('accesslog_timestamp' => array('timestamp')),
+    'indexes' => array(
+      'accesslog_timestamp' => array('timestamp'),
+      'uid' => array('uid'),
+    ),
     'primary key' => array('aid'),
   );
 
diff --git a/modules/system/system.install b/modules/system/system.install
index e7599c41d..18fed5c79 100644
--- a/modules/system/system.install
+++ b/modules/system/system.install
@@ -588,6 +588,9 @@ function system_schema() {
         'default' => 0)
       ),
     'primary key' => array('fid'),
+    'indexes' => array(
+      'allow' => array('event', 'hostname', 'timestamp'),
+    ),
     );
 
   $schema['history'] = array(
@@ -610,6 +613,9 @@ function system_schema() {
         'default' => 0)
       ),
     'primary key' => array('uid', 'nid'),
+    'indexes' => array(
+      'nid' => array('nid'),
+    ),
     );
   $schema['menu_router'] = array(
     'description' => t('Maps paths to various callbacks (access, page and title)'),
@@ -2386,13 +2392,16 @@ function system_update_6022() {
   db_add_index($ret, 'files', 'timestamp', array('timestamp'));
 
   // Rename the file_revisions table to upload then add nid column. Since we're
-  // changing the table name we need to drop and re-add the vid index so both
-  // pgsql ends up with the correct index name.
+  // changing the table name we need to drop and re-add the indexes and
+  // the primary key so both mysql and pgsql end up with the correct index
+  // names.
+  db_drop_primary_key($ret, 'file_revisions');
   db_drop_index($ret, 'file_revisions', 'vid');
   db_rename_table($ret, 'file_revisions', 'upload');
   db_add_field($ret, 'upload', 'nid', array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0));
   db_add_index($ret, 'upload', 'nid', array('nid'));
-  db_add_index($ret, 'upload', 'vid', array('vid'));
+  db_add_primary_key($ret, 'upload', array('vid', 'fid'));
+  db_add_index($ret, 'upload', 'fid', array('fid'));
 
   // The nid column was renamed to uid. Use the old nid to find the node's uid.
   update_sql('UPDATE {files} SET uid = (SELECT n.uid FROM {node} n WHERE {files}.uid = n.nid)');
@@ -2762,6 +2771,100 @@ function system_update_6042() {
 }
 
 /**
+ * Update table indices to make them more rational and useful.
+ */
+function system_update_6043() {
+  $ret = array();
+  // Required modules first.
+  // Add new system module indexes.
+  db_add_index($ret, 'flood', 'allow', array('event', 'hostname', 'timestamp'));
+  db_add_index($ret, 'history', 'nid', array('nid'));
+  // Change length of theme field in {blocks} to be consistent with module, and
+  // to avoid a MySQL error regarding a too-long index.  Also add new indices.
+  db_change_field($ret, 'blocks', 'theme', 'theme', array('type' => 'varchar', 'length' => 64, 'not null' => TRUE, 'default' => ''),array(
+                  'unique keys' => array('tmd' => array('theme', 'module', 'delta'),),
+                  'indexes' => array('list' => array('theme', 'status', 'region', 'weight', 'module'),),));
+  db_add_index($ret, 'blocks_roles', 'rid', array('rid'));
+  // Improve filter module indices.
+  db_drop_index($ret, 'filters', 'weight');
+  db_add_unique_key($ret, 'filters', 'fmd', array('format', 'module', 'delta'));
+  db_add_index($ret, 'filters', 'list', array('format', 'weight', 'module', 'delta'));
+  // Drop unneeded keys form the node table.
+  db_drop_index($ret, 'node', 'status');
+  db_drop_unique_key($ret, 'node', 'nid_vid');
+  // Improve user module indices.
+  db_add_unique_key($ret, 'users', 'mail', array('mail'));
+  db_add_index($ret, 'users_roles', 'rid', array('rid'));
+
+  // Optional modules - need to check if the tables exist.
+  // Alter aggregator module's tables primary keys to make them more useful.
+  if (db_table_exists('aggregator_category_feed')) {
+    db_drop_primary_key($ret, 'aggregator_category_feed');
+    db_add_primary_key($ret, 'aggregator_category_feed', array('cid', 'fid'));
+    db_add_index($ret, 'aggregator_category_feed', 'fid', array('fid'));
+  }
+  if (db_table_exists('aggregator_category_item')) {
+    db_drop_primary_key($ret, 'aggregator_category_item');
+    db_add_primary_key($ret, 'aggregator_category_item', array('cid', 'iid'));
+    db_add_index($ret, 'aggregator_category_item', 'iid', array('iid'));
+  }
+  // Alter contact module's table to add an index.
+  if (db_table_exists('contact')) {
+    db_add_index($ret, 'contact', 'list', array('weight', 'category'));
+  }
+  // Alter locale table to add a primary key, drop an index.
+  if (db_table_exists('locales_target')) {
+    db_add_primary_key($ret, 'locales_target', array('language', 'lid', 'plural'));
+  }
+  // Alter a poll module table to add a primary key.
+  if (db_table_exists('poll_votes')) {
+    db_drop_index($ret, 'poll_votes', 'nid');
+    db_add_primary_key($ret, 'poll_votes', array('nid', 'uid', 'hostname'));
+  }
+  // Alter a profile module table to add a primary key.
+  if (db_table_exists('profile_values')) {
+    db_drop_index($ret, 'profile_values', 'uid');
+    db_drop_index($ret, 'profile_values', 'fid');
+    db_change_field($ret,'profile_values' ,'fid', 'fid', array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0,), array('indexes' => array('fid' => array('fid'),)));
+    db_change_field($ret,'profile_values' ,'uid', 'uid', array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0,));
+    db_add_primary_key($ret, 'profile_values', array('uid', 'fid'));
+  }
+  // Alter a statistics module table to add an index.
+  if (db_table_exists('accesslog')) {
+    db_add_index($ret, 'accesslog', 'uid', array('uid'));
+  }
+  // Alter taxonomy module's tables.
+  if (db_table_exists('term_data')) {
+    db_drop_index($ret, 'term_data', 'vid');
+    db_add_unique_key($ret, 'term_data', 'vid_name', array('vid', 'name'));
+    db_add_index($ret, 'term_data', 'taxonomy_tree', array('vid', 'weight', 'name'));
+  }
+  if (db_table_exists('term_node')) {
+    db_drop_primary_key($ret, 'term_node');
+    db_drop_index($ret, 'term_node', 'tid');
+    db_add_primary_key($ret, 'term_node', array('tid', 'vid'));
+  }
+  if (db_table_exists('term_relation')) {
+    db_drop_index($ret, 'term_relation', 'tid1');
+    db_add_unique_key($ret, 'term_relation', 'tid1_tid2', array('tid1', 'tid2'));
+  }
+  if (db_table_exists('term_synonym')) {
+    db_drop_index($ret, 'term_synonym', 'name');
+    db_add_unique_key($ret, 'term_synonym', 'name_tid', array('name', 'tid'));
+  }
+  if (db_table_exists('vocabulary')) {
+    db_add_index($ret, 'vocabulary', 'list', array('weight', 'name'));
+  }
+  if (db_table_exists('vocabulary_node_types')) {
+    db_drop_primary_key($ret, 'vocabulary_node_types');
+    db_add_primary_key($ret, 'vocabulary_node_types', array('type', 'vid'));
+    db_add_index($ret, 'vocabulary_node_types', 'vid', array('vid'));
+  }
+
+  return $ret;
+}
+
+/**
  * @} End of "defgroup updates-5.x-to-6.x"
  * The next series of updates should start at 7000.
  */
diff --git a/modules/taxonomy/taxonomy.install b/modules/taxonomy/taxonomy.install
index d1e2c249f..18ee82e1f 100644
--- a/modules/taxonomy/taxonomy.install
+++ b/modules/taxonomy/taxonomy.install
@@ -43,7 +43,10 @@ function taxonomy_schema() {
       ),
     ),
     'primary key' => array('tid'),
-    'indexes' => array('vid' => array('vid')),
+    'unique keys' => array(
+      'vid_name' => array('vid', 'name'),
+    ),
+    'indexes' => array('taxonomy_tree' => array('vid', 'weight', 'name')),
   );
 
   $schema['term_hierarchy'] = array(
@@ -66,7 +69,6 @@ function taxonomy_schema() {
     ),
     'indexes' => array(
       'parent' => array('parent'),
-      'tid' => array('tid'),
     ),
     'primary key' => array('tid', 'parent'),
   );
@@ -97,11 +99,10 @@ function taxonomy_schema() {
       ),
     ),
     'indexes' => array(
-      'nid' => array('nid'),
       'vid' => array('vid'),
-      'tid' => array('tid'),
+      'nid' => array('nid'),
     ),
-    'primary key' => array('vid', 'tid', 'nid'),
+    'primary key' => array('tid', 'vid'),
   );
 
   $schema['term_relation'] = array(
@@ -127,8 +128,10 @@ function taxonomy_schema() {
         'description' => t('The {term_data}.tid of the second term in a relationship.'),
       ),
     ),
+    'unique keys' => array(
+      'tid1_tid2' => array('tid1', 'tid2'),
+    ),
     'indexes' => array(
-      'tid1' => array('tid1'),
       'tid2' => array('tid2'),
     ),
     'primary key' => array('trid'),
@@ -157,8 +160,10 @@ function taxonomy_schema() {
         'description' => t('The name of the synonym.'),
       ),
     ),
+    'unique keys' => array(
+      'name_tid' => array('name', 'tid'),
+    ),
     'indexes' => array(
-      'name' => array(array('name', 3)),
       'tid' => array('tid'),
     ),
     'primary key' => array('tsid'),
@@ -249,6 +254,9 @@ function taxonomy_schema() {
       ),
     ),
     'primary key' => array('vid'),
+    'indexes' => array(
+      'list' => array('weight', 'name'),
+    ),
   );
 
   $schema['vocabulary_node_types'] = array(
@@ -269,7 +277,10 @@ function taxonomy_schema() {
         'description' => t('The {node}.type of the node type for which the vocabulary may be used.'),
       ),
     ),
-    'primary key' => array('vid', 'type'),
+    'primary key' => array('type', 'vid'),
+    'indexes' => array(
+      'vid' => array('vid'),
+    ),
   );
 
   return $schema;
diff --git a/modules/upload/upload.install b/modules/upload/upload.install
index 08638a7c2..2a5fa213f 100644
--- a/modules/upload/upload.install
+++ b/modules/upload/upload.install
@@ -68,8 +68,11 @@ function upload_schema() {
         'description' => t('Weight of this upload in relation to other uploads in this node.'),
       ),
     ),
-    'primary key' => array('fid', 'vid'),
-    'indexes' => array('vid' => array('vid'), 'nid' => array('nid')),
+    'primary key' => array('vid', 'fid'),
+    'indexes' => array(
+      'fid' => array('fid'),
+      'nid' => array('nid'),
+    ),
   );
 
   return $schema;
diff --git a/modules/user/user.install b/modules/user/user.install
index 3522cc91f..8bdcfc963 100644
--- a/modules/user/user.install
+++ b/modules/user/user.install
@@ -254,7 +254,10 @@ function user_schema() {
       'access' => array('access'),
       'created' => array('created')
     ),
-    'unique keys' => array('name' => array('name')),
+    'unique keys' => array(
+      'name' => array('name'),
+      'mail' => array('mail'),
+    ),
     'primary key' => array('uid'),
   );
 
@@ -277,6 +280,9 @@ function user_schema() {
       ),
     ),
     'primary key' => array('uid', 'rid'),
+    'indexes' => array(
+      'rid' => array('rid'),
+    ),
   );
 
   return $schema;