6c8386587f9dc2eb3a34db1018d8fd52361c2b4d
hiram
  Fri Jun 12 13:48:55 2026 -0700
better indexes on assemblyList table to improve search performance

diff --git src/hg/lib/assemblyList.sql src/hg/lib/assemblyList.sql
index 9fc095bd7f2..09757f2e2e6 100644
--- src/hg/lib/assemblyList.sql
+++ src/hg/lib/assemblyList.sql
@@ -1,25 +1,30 @@
 # assemblyList.sql was originally generated by the autoSql program, which also 
 # generated assemblyList.c and assemblyList.h.  This creates the database representation of
 # an object which can be loaded and saved from RAM in a fairly 
 # automatic way.
 
 #listing all UCSC genomes, and all NCBI assemblies, with search priority, and status if browser available or can be requested
 CREATE TABLE assemblyList (
     name varchar(255),	# UCSC genome: dbDb name or GenArk/NCBI accession
     priority int unsigned,	# assigned search priority
     commonName varchar(511),	# a common name
     scientificName varchar(511),	# binomial scientific name
     taxId int unsigned,	# Entrez taxon ID: www.ncbi.nlm.nih.gov/taxonomy/?term=xxx
     clade varchar(255),	# approximate clade: primates mammals birds fish ... etc ...
     description varchar(1023),	# other description text
     browserExists tinyint unsigned,	# 1 == this assembly is available at UCSC, 0 == can be requested
     hubUrl varchar(511),	# path name to hub.txt: GCF/000/001/405/GCF_000001405.39/hub.txt
     year int unsigned,	# year of assembly construction
     refSeqCategory varchar(31),	# one of: reference, representative or na
     versionStatus varchar(15),	# one of: latest, replaced or suppressed
     assemblyLevel varchar(15),	# one of: complete, chromosome, scaffold or contig
     haplotypes varchar(511),	# related haplotype assembly when available, comma separated list for polyploid
               #Indices
     FULLTEXT gIdx (name, commonName, scientificName, clade, description, refSeqCategory, versionStatus, assemblyLevel, haplotypes),
-    PRIMARY KEY(name)
+    PRIMARY KEY(name),
+    INDEX idxBrowserExists (browserExists),
+    INDEX idxRefSeqCategory (refSeqCategory),
+    INDEX idxVersionStatus (versionStatus),
+    INDEX idxAssemblyLevel (assemblyLevel),
+    INDEX idxFilters (browserExists, refSeqCategory, versionStatus, assemblyLevel)
 );