afabfb46d123af83f0b66b48fb2d229c9fbc4df7
hiram
  Wed Aug 14 16:32:27 2024 -0700
efficient use of the SQL count function when statsOnly refs #32897

diff --git src/hg/hubApi/findGenome.c src/hg/hubApi/findGenome.c
index 267e352..e4d0825 100644
--- src/hg/hubApi/findGenome.c
+++ src/hg/hubApi/findGenome.c
@@ -34,139 +34,145 @@
 | commonName     | varchar(511)        | YES  |     | NULL    |       |
 | scientificName | varchar(511)        | YES  |     | NULL    |       |
 | taxId          | int(10) unsigned    | YES  |     | NULL    |       |
 | clade          | varchar(255)        | YES  |     | NULL    |       |
 | description    | varchar(1023)       | YES  |     | NULL    |       |
 | browserExists  | tinyint(3) unsigned | YES  |     | NULL    |       |
 | hubUrl         | varchar(511)        | YES  |     | NULL    |       |
 +----------------+---------------------+------+-----+---------+-------+
 */
 
 static long long sqlJsonOut(struct jsonWrite *jw, struct sqlResult *sr)
 /* given a sqlResult, walk through the rows and output the json */
 {
 int itemCount = 0;
 char **row;
-if (statsOnly)	// only counting, nothing returned
-    {
-    while ((row = sqlNextRow(sr)) != NULL)
-        ++itemCount;
-    }
-else
-    {
 while ((row = sqlNextRow(sr)) != NULL)
     {
     struct assemblyList *el = assemblyListLoadWithNull(row);
     jsonWriteObjectStart(jw, el->name);
     jsonWriteNumber(jw, "priority", (long long)el->priority);
     jsonWriteString(jw, "commonName", el->commonName);
     jsonWriteString(jw, "scientificName", el->scientificName);
     jsonWriteNumber(jw, "taxId", (long long)el->taxId);
     jsonWriteString(jw, "clade", el->clade);
     jsonWriteString(jw, "description", el->description);
     if (1 == *el->browserExists)
         jsonWriteBoolean(jw, "browserExists", TRUE);
     else
         jsonWriteBoolean(jw, "browserExists", FALSE);
     if (isEmpty(el->hubUrl))
         jsonWriteString(jw, "hubUrl", NULL);
     else
         jsonWriteString(jw, "hubUrl", el->hubUrl);
     jsonWriteObjectEnd(jw);
     ++itemCount;
     }
-    }
 return (itemCount);
 }
 
 static long long multipleWordSearch(struct sqlConnection *conn, char **words, int wordCount, struct jsonWrite *jw, long long *totalMatchCount)
 /* perform search on multiple words, prepare json and return number of matches */
 {
 long long itemCount = 0;
 *totalMatchCount = 0;
 if (wordCount < 0)
     return itemCount;
 
 /* get the words[] into a single string */
 struct dyString *queryDy = dyStringNew(128);
 dyStringPrintf(queryDy, "%s", words[0]);
 for (int i = 1; i < wordCount; ++i)
     dyStringPrintf(queryDy, " %s", words[i]);
 
 struct dyString *query = dyStringNew(64);
 sqlDyStringPrintf(query, "SELECT COUNT(*) FROM %s WHERE MATCH(name, commonName, scientificName, clade, description) AGAINST ('%s' IN BOOLEAN MODE)", asmListTable, queryDy->string);
 if (!allowAll)
     sqlDyStringPrintf(query, " AND browserExists=1");
 long long matchCount = sqlQuickLongLong(conn, query->string);
 if (matchCount > 0)
     {
     *totalMatchCount = matchCount;
+    if (statsOnly)	// only counting, nothing returned
+	{	// the LIMIT would limit results to maxItemsOutput
+	itemCount = min(maxItemsOutput, matchCount);
+	}	// when less than totalMatchCount
+    else
+	{
 	dyStringFree(&query);
 	query = dyStringNew(64);
 	sqlDyStringPrintf(query, "SELECT * FROM %s WHERE MATCH(name, commonName, scientificName, clade, description) AGAINST ('%s' IN BOOLEAN MODE)", asmListTable, queryDy->string);
 	if (!allowAll)
 	    sqlDyStringPrintf(query, " AND browserExists=1");
 	sqlDyStringPrintf(query, " ORDER BY priority LIMIT %d;", maxItemsOutput);
 	struct sqlResult *sr = sqlGetResult(conn, query->string);
 	itemCount = sqlJsonOut(jw, sr);
 	sqlFreeResult(&sr);
 	dyStringFree(&query);
 	}
+    }
 return itemCount;
 }
 
 static long long oneWordSearch(struct sqlConnection *conn, char *searchWord, struct jsonWrite *jw, long long *totalMatchCount)
 /* perform search on a single word, prepare json and return number of matches
  *   and number of potential matches totalMatchCount
  */
 {
 long long itemCount = 0;
 *totalMatchCount = 0;
 
 struct dyString *query = dyStringNew(64);
 sqlDyStringPrintf(query, "SELECT COUNT(*) FROM %s WHERE MATCH(name, commonName, scientificName, clade, description) AGAINST ('%s' IN BOOLEAN MODE)", asmListTable, searchWord);
 if (!allowAll)
     sqlDyStringPrintf(query, " AND browserExists=1");
 
 long long matchCount = sqlQuickLongLong(conn, query->string);
 boolean prefixSearch = FALSE;
 if (matchCount < 1)	/* no match, add the * wild card match to make a prefix match */
     {
     dyStringFree(&query);
     query = dyStringNew(64);
     sqlDyStringPrintf(query, "SELECT COUNT(*) FROM %s WHERE MATCH(name, commonName, scientificName, clade, description) AGAINST ('%s*' IN BOOLEAN MODE)", asmListTable, searchWord);
     if (!allowAll)
         sqlDyStringPrintf(query, " AND browserExists=1");
     matchCount = sqlQuickLongLong(conn, query->string);
     if (matchCount > 0)
 	prefixSearch = TRUE;
     }
-if (matchCount < 1)
+if (matchCount < 1)	// nothing found, returning zero
     return itemCount;
 *totalMatchCount = matchCount;
 
+if (statsOnly)	// only counting, nothing returned
+    {	// the LIMIT would limit results to maxItemsOutput
+    itemCount = min(maxItemsOutput, matchCount);
+    }	// when less than totalMatchCount
+else
+    {
     dyStringFree(&query);
     query = dyStringNew(64);
 
     sqlDyStringPrintf(query, "SELECT * FROM %s WHERE MATCH(name, commonName, scientificName, clade, description) AGAINST ('%s%s' IN BOOLEAN MODE)", asmListTable, searchWord, prefixSearch ? "*" : "");
     if (!allowAll)
     sqlDyStringPrintf(query, " AND browserExists=1");
     sqlDyStringPrintf(query, " ORDER BY priority LIMIT %d;", maxItemsOutput);
     struct sqlResult *sr = sqlGetResult(conn, query->string);
     itemCount = sqlJsonOut(jw, sr);
     sqlFreeResult(&sr);
     dyStringFree(&query);
+    }
 
 return itemCount;
 }	/*	static long long oneWordSearch(struct sqlConnection *conn, char *searchWord, struct jsonWrite *jw) */
 
 static void elapsedTime(struct jsonWrite *jw)
 {
 long nowTime = clock1000();
 long elapsedTimeMs = nowTime - enteredMainTime;
 jsonWriteNumber(jw, "elapsedTimeMs", elapsedTimeMs);
 }
 
 void apiFindGenome(char *pathString[MAX_PATH_INFO])
 /* 'findGenome' function */
 {
 char *searchString = cgiOptionalString(argGenomeSearchTerm);