src/hg/lib/jksql.c 1.131

Index: src/hg/lib/jksql.c
===================================================================
RCS file: /projects/compbio/cvsroot/kent/src/hg/lib/jksql.c,v
retrieving revision 1.130
retrieving revision 1.131
diff -b -B -U 4 -r1.130 -r1.131
--- src/hg/lib/jksql.c	29 Apr 2009 22:27:06 -0000	1.130
+++ src/hg/lib/jksql.c	20 Aug 2009 18:15:41 -0000	1.131
@@ -536,18 +536,39 @@
 
 struct slName *sqlListTables(struct sqlConnection *conn)
 /* Return list of tables in database associated with conn. */
 {
-struct sqlResult *sr = sqlGetResult(conn, "show tables");
+struct sqlResult *sr;
 char **row;
 struct slName *list = NULL, *el;
-while ((row = sqlNextRow(sr)) != NULL)
+
+if (sqlTableExists(conn, "tableList"))
+    {
+    // mysql does not cache "show tables", so use a cached run of show tables in the tableList table (if it exists).
+    // Table is loaded thus:
+    //
+    //   hgsql hg18 -e 'show tables' > tables.txt
+    //   CREATE TABLE tableList (name varchar(255) NOT NULL, INDEX(name));
+    //   load data local infile 'tables.txt' into table tableList;
+
+    sr = sqlGetResult(conn, "select * from tableList order by name desc");
+    while ((row = sqlNextRow(sr)) != NULL)
+        {
+        el = slNameNew(row[0]);
+        slAddHead(&list, el);
+        }
+    }
+else
+    {
+    sr = sqlGetResult(conn, "show tables");
+    while ((row = sqlNextRow(sr)) != NULL)
     {
     el = slNameNew(row[0]);
     slAddHead(&list, el);
     }
+    slReverse(&list);
+    }
 sqlFreeResult(&sr);
-slReverse(&list);
 return list;
 }
 
 struct slName *sqlListFields(struct sqlConnection *conn, char *table)