768aadaba072ec61be529f67f44bd710f313c358
tdreszer
  Tue Jun 4 13:23:40 2013 -0700
Turns out the single index on both var and val does work fin.  The thing that triggers the inefficiency is the length of val that is included.  It was val(32) and that was slow.  Setting it to val(64) and it is blinfdlingly fast on mysql 5.6.10. Ref 10910.
diff --git src/hg/lib/mdb.c src/hg/lib/mdb.c
index 0e810f2..bcae6d9 100644
--- src/hg/lib/mdb.c
+++ src/hg/lib/mdb.c
@@ -905,32 +905,31 @@
 }
 
 // ------ Table name and creation ------
 
 void mdbReCreate(struct sqlConnection *conn,char *tblName,boolean testOnly)
 // Creates ore Recreates the named mdb.
 {
 char *sqlCreate =
     "# Contains metadata for a table, file or other objects.\n"
     "CREATE TABLE %s (\n"
     "    obj varchar(255) not null,      # Object name or ID.\n"
     "    var varchar(255) not null,      # Metadata variable name.\n"
     "    val varchar(2048) not null,     # Metadata value.\n"
     "  #Indices\n"
     "    PRIMARY KEY(obj,var),\n"
-    "    INDEX varKey (var),\n"
-    "    INDEX valKey (val(64))\n"
+    "    INDEX varKey (var,val(64))\n"
     ")";
 
 if (sqlTableExists(conn,tblName))
     verbose(2, "Table '%s' already exists.  It will be recreated.\n",tblName);
 
 struct dyString *dy = newDyString(512);
 sqlDyStringPrintf(dy, sqlCreate, tblName);
 verbose(2, "Requesting table creation:\n%s;\n", dyStringContents(dy));
 if (!testOnly)
     sqlRemakeTable(conn, tblName, dyStringContents(dy));
 
 dyStringFree(&dy);
 }
 
 #define HG_CONF_SANDBOX_MDB "db.metaDb"