5197ebd63b54192a79e5f001a5cb18d89822b542
chmalee
  Wed May 29 13:42:32 2024 -0700
Improve genbank search performance, mostly by optimizing mysql queries
down from one query per findSpec result into a single query that checks
all the findSpec results

diff --git src/hg/checkHgFindSpec/checkHgFindSpec.c src/hg/checkHgFindSpec/checkHgFindSpec.c
index 0f52e46..8fc1f9d 100644
--- src/hg/checkHgFindSpec/checkHgFindSpec.c
+++ src/hg/checkHgFindSpec/checkHgFindSpec.c
@@ -23,64 +23,67 @@
 struct cart *cart = NULL;
 
 /* Caches for searching */
 extern struct trackDb *hgFindTdbList;
 extern struct grp *hgFindGrpList;
 extern struct hash *hgFindGroupHash;
 extern struct hash *hgFindTrackHash;
 
 /* Command line option specifications */
 static struct optionSpec optionSpecs[] = {
     {"showSearches",    OPTION_BOOLEAN},
     {"checkTermRegex",  OPTION_BOOLEAN},
     {"exampleFor",      OPTION_STRING},
     {"checkIndexes",    OPTION_BOOLEAN},
     {"makeExamples",    OPTION_BOOLEAN},
+    {"noHtml",          OPTION_BOOLEAN},
     {NULL, 0}
 };
 
 
 void usage()
 {
 errAbort(
 "checkHgFindSpec - test and describe search specs in hgFindSpec tables.\n"
 "usage:\n"
 "  checkHgFindSpec database [options | termToSearch]\n"
 "If given a termToSearch, displays the list of tables that will be searched\n"
 "and how long it took to figure that out; then performs the search and the\n"
 "time it took.\n"
 "options:\n"
 "  -showSearches       Show the order in which tables will be searched in\n"
 "                      general.  [This will be done anyway if no\n"
 "                      termToSearch or options are specified.]\n"
 "  -checkTermRegex     For each search spec that includes a regular\n"
 "                      expression for terms, make sure that all values of\n"
 "                      the table field to be searched match the regex.  (If\n"
 "                      not, some of them could be excluded from searches.)\n"
 "  -checkIndexes       Make sure that an index is defined on each field to\n"
 "                      be searched.\n"
+"  -noHtml             Do not print the html results list, just figure out\n"
+"                      the search results and print timing\n"
 /**#*** IMPLEMENT ME!
 "  -exampleFor=search  Randomly choose a term for the specified search (from\n"
 "                      the target table for the search).  Search for it.\n"
 "  -makeExamples       Print out an HTML table of example positions\n"
 "                      (suitable for a gateway description.html).\n"
 */
 	 );
 }
 
 
-boolean reportSearch(char *termToSearch)
+boolean reportSearch(char *termToSearch, boolean noHtml)
 /* Show the list of tables that will be searched, and how long it took to 
  * figure that out.  Then do the search; show results and time required. */
 //#*** this doesn't handle ; in termToSearch (until the actual search)
 {
 struct hgFindSpec *shortList = NULL, *longList = NULL;
 struct hgFindSpec *hfs = NULL;
 int startMs = 0, endMs = 0;
 boolean gotError = FALSE;
 char *chrom = NULL;
 int chromStart = 0, chromEnd = 0;
 
 hgFindSpecGetAllSpecs(database, &shortList, &longList);
 puts("\n");
 startMs = clock1000();
 for (hfs = shortList;  hfs != NULL;  hfs = hfs->next)
@@ -152,32 +155,31 @@
     endMs = clock1000();
     if (isNotEmpty(dyWarn->string))
         warn("%s", dyWarn->string);
     if (hgp->singlePos != NULL)
 	{
 	struct hgPos *pos = hgp->singlePos;
 	char *table = "[No reported table!]";
 	char *name = pos->name ? pos->name : "";
 	char *browserName = pos->browserName ? pos->browserName : "";
 	char *description = pos->description ? pos->description : "";
 	if (hgp->tableList != NULL)
 	    table = hgp->tableList->name;
 	printf("\nSingle result for %s from %s: %s:%d-%d   [%s | %s | %s]\n",
 	       termToSearch, table, chrom, chromStart+1, chromEnd,
 	       name, browserName, description);
-	}
-    else
+	} else if (!noHtml)
         hgPositionsHtml(database, hgp, "checkHgFindSpec", cart);
     printf("\nTook %dms to search for %s.\n\n",
 	   endMs - startMs, termToSearch);
     }
 
 hgFindSpecFreeList(&shortList);
 hgFindSpecFreeList(&longList);
 return(gotError);
 }
 
 static char *getFieldFromQuery(char *query, char *searchName)
 /* Get the value of the field that's being searched in query. */
 {
 char *ptr = strstr(query, " where ");
 char *field = NULL;
@@ -354,83 +356,85 @@
 }
 
 boolean doMakeExamples()
 /* Print out an HTML table of position examples for description.html. */
 {
 boolean gotError = FALSE;
 
 errAbort("Sorry, -makeExamples not implemented yet.");
 
 return(gotError);
 }
 
 
 int checkHgFindSpec(char *db, char *termToSearch, boolean showSearches,
 		    boolean checkTermRegex, char *exampleFor,
-		    boolean checkIndexes, boolean makeExamples)
+		    boolean checkIndexes, boolean makeExamples, boolean noHtml)
 /* Perform searches/checks as specified, summarize errors, 
  * return nonzero if there are errors. */
 {
 boolean gotError = FALSE;
 
 database = db;
 initGenbankTableNames(db);
 
 if (isNotEmpty(termToSearch))
-    gotError |= reportSearch(termToSearch);
+    gotError |= reportSearch(termToSearch, noHtml);
 if (showSearches)
-    gotError |= reportSearch(NULL);
+    gotError |= reportSearch(NULL, noHtml);
 if (checkTermRegex)
     gotError |= doCheckTermRegex();
 if (isNotEmpty(exampleFor))
     {
     termToSearch = getExampleFor(exampleFor);
-    gotError |= reportSearch(termToSearch);
+    gotError |= reportSearch(termToSearch, noHtml);
     }
 if (checkIndexes)
     gotError |= doCheckIndexes();
 if (makeExamples)
     gotError |= doMakeExamples();
 
 return gotError;
 }
 
 
 /* Just a placeholder -- we don't do anything with the cart. */
 char *excludeVars[] = { NULL };
 
 
 int main(int argc, char *argv[])
 {
 char   *termToSearch   = NULL;
 boolean showSearches   = FALSE;
 boolean checkTermRegex = FALSE;
 char   *exampleFor     = NULL;
 boolean checkIndexes   = FALSE;
 boolean makeExamples   = FALSE;
+boolean noHtml = FALSE;
 
 optionInit(&argc, argv, optionSpecs);
 /* Allow "checkHgFindSpec db" or "checkHgFindSpec db termToSearch" usage: */
 if (termToSearch == NULL && argc == 3)
     {
     termToSearch = argv[2];
     argc--;
     }
 if (argc != 2)
     usage();
 
 showSearches = optionExists("showSearches");
 checkTermRegex = optionExists("checkTermRegex");
 exampleFor = optionVal("exampleFor", exampleFor);
 checkIndexes = optionExists("checkIndexes");
 makeExamples = optionExists("makeExamples");
+noHtml = optionExists("noHtml");
 
 /* If no termToSearch or options are specified, do showSearches. */
 if (termToSearch == NULL && exampleFor == NULL &&
     !showSearches && !checkTermRegex && !checkIndexes && !makeExamples)
     showSearches = TRUE;
 
 cgiSpoof(&argc, argv);
 cart = cartAndCookie(hUserCookie(), excludeVars, NULL);
 return checkHgFindSpec(argv[1], termToSearch, showSearches, checkTermRegex,
-		       exampleFor, checkIndexes, makeExamples);
+		       exampleFor, checkIndexes, makeExamples, noHtml);
 }