* Database Information WARNING: This document contains information on the GN databases which will change over time. The GN database is currently MySQL based and, while efficient, contains a number of design choices we want to grow 'out' of. Especially with an eye on reproducibility we want to introduce versioning. So do not treat the information in this document as a final way of accessing data. It is better to use the [[https://github.com/genenetwork/gn_server/blob/master/doc/API.md][REST API]]. * The small test database (2GB) The default install comes with a smaller database which includes a number of the BXD's and the Human liver dataset (GSE9588). * GeneNetwork database ** Estimated table sizes with metadata comment select table_name,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` from information_schema.TABLES where table_schema = "db_webqtl" order by data_length; +-------------------------+------------+ | table_name | Size in MB | +-------------------------+------------+ | ProbeSetData | 59358.80 | DataStrainValues (5G traits) <- id, strain, value | ProbeData | 22405.44 | mRNAStrainValues <- individual probe data (mRNA) [GN1,GN3] | ProbeSetSE | 14551.02 | ProbeSetStrainError <- SE values aligns with ProbeSetData | ProbeSetXRef | 4532.89 | PrecomputeLRS <- precomputed LRS values | ProbeSE | 6263.83 | DataStrainError <- trait SE [GN3] | ProbeSet | 2880.21 | ProbeSetInfo <- trait, e.g. 100001_at | ProbeSetFreeze | 0.22 | DatasetInfo <- dataset, e.g. "Hippocampus_BXD_Jun06" | Probe | 2150.30 | ProbeInfo <- trait info incl sequence | ProbeFreeze | 0.06 | | Phenotype | 6.50 | PhenotypeMeta <- "Hippocampus weight" | ProbeXRef | 743.38 | ProbeFreezeDataIDs <- link ProbeFreezeId,ProbeId with DataId | Datasets | 2.31 | DatasetMeta <- "Data generated by..." | NStrain | 4.80 | StrainCountDataId <- Strains used in dataset | Strain | 1.07 | StrainNames <- with species ID and alias | TissueProbeSetData | 74.42 | <- link Id,TissueID with value | TissueProbeSetXRef | 14.73 | | PublishData | 22.54 | PublishStrainValues <- id, strainId, value (3M traits) | PublishSE | 4.71 | Published SE (300K traits) | PublishXRef | 2.18 | List of changes on editing data (mostly Rob) | InbredSet | 0.01 | InbredSetMeta -> Id,SpeciesId,FullName +-------------------------+------------+ Less commonly used tables: +-------------------------+------------+ | table_name | Size in MB | +-------------------------+------------+ | LCorrRamin3 | 18506.53 | | SnpAll | 15484.67 | | SnpPattern | 9177.05 | | QuickSearch | 5972.86 | | GenoData | 3291.91 | Strain by genotype - only used in GN1 | CeleraINFO_mm6 | 989.80 | | pubmedsearch | 1032.50 | | GeneRIF_BASIC | 448.54 | | BXDSnpPosition | 224.44 | | EnsemblProbe | 133.66 | | EnsemblProbeLocation | 105.49 | | Genbank | 37.71 | | AccessLog | 42.38 | | GeneList | 34.11 | | Geno | 33.90 | Marker probe info (incl. sequence) | MachineAccessLog | 28.34 | | IndelAll | 22.42 | | ProbeH2 | 13.26 | | GenoXRef | 22.83 | | TempData | 8.35 | | GeneList_rn3 | 5.54 | | GORef | 4.97 | | temporary | 3.59 | | InfoFiles | 3.32 | | Publication | 3.42 | | Homologene | 5.69 | | GeneList_rn33 | 2.61 | | GeneRIF | 2.18 | | Vlookup | 1.87 | | H2 | 2.18 | | IndelXRef | 2.91 | | GeneMap_cuiyan | 0.51 | | user_collection | 0.30 | | CaseAttributeXRef | 0.44 | | StrainXRef | 0.56 | | GeneIDXRef | 0.77 | | Docs | 0.17 | | News | 0.17 | | GeneRIFXRef | 0.24 | | Sample | 0.06 | | login | 0.06 | | user | 0.04 | | TableFieldAnnotation | 0.05 | | DatasetMapInvestigator | 0.05 | | User | 0.04 | | TableComments | 0.02 | | Investigators | 0.02 | | DBList | 0.03 | | Tissue | 0.02 | | GeneChip | 0.01 | | GeneCategory | 0.01 | | SampleXRef | 0.01 | | SnpAllele_to_be_deleted | 0.00 | | Organizations | 0.01 | | PublishFreeze | 0.00 | | GenoFreeze | 0.00 | Used for public/private | Chr_Length | 0.01 | | SnpSource | 0.00 | | AvgMethod | 0.00 | | Species | 0.00 | | Dataset_mbat | 0.00 | | TissueProbeFreeze | 0.00 | | EnsemblChip | 0.00 | | TissueProbeSetFreeze | 0.01 | | UserPrivilege | 0.00 | | CaseAttribute | 0.00 | | MappingMethod | 0.00 | | DBType | 0.00 | | InfoFilesUser_md5 | 0.00 | | GenoCode | 0.00 | | DatasetStatus | 0.00 | | GeneChipEnsemblXRef | 0.00 | | GenoSE | 0.00 | | user_openids | 0.00 | | roles_users | 0.00 | | role | 0.00 | | Temp | NULL | +-------------------------+------------+ 97 rows in set, 1 warning (0.01 sec) All *Data tables are large ** User access According to the meta data: This table tracks access time and IP addresses. Used for logging in registered users and tracking cookies. # GN1 uses access table and GN2 uses user table (true/false?) select * from AccessLog limit 5; +-------+---------------------+----------------+ | id | accesstime | ip_address | +-------+---------------------+----------------+ | 12174 | 2003-10-28 02:17:41 | 130.120.104.71 | | 12173 | 2003-10-28 02:16:27 | 130.120.104.71 | | 3 | 2003-02-22 07:38:33 | 192.117.159.1 | | 4 | 2003-02-22 07:49:13 | 192.117.159.1 | | 5 | 2003-02-22 07:51:08 | 192.117.159.1 | +-------+---------------------+----------------+ select * from AccessLog order by accesstime desc limit 5; +---------+---------------------+---------------+ | id | accesstime | ip_address | +---------+---------------------+---------------+ | 1025735 | 2016-02-08 14:23:29 | 100.43.81.157 | | 1025734 | 2016-02-08 13:54:28 | 180.76.15.144 | | 1025733 | 2016-02-08 13:43:37 | 66.249.65.217 | | 1025732 | 2016-02-08 13:39:50 | 66.249.65.217 | | 1025731 | 2016-02-08 13:15:46 | 66.249.65.217 | +---------+---------------------+---------------+ Quite a few trait page hits: select count(*) from AccessLog; +----------+ | count(*) | +----------+ | 1025685 | +----------+ show indexes from AccessLog; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | AccessLog | 0 | PRIMARY | 1 | id | A | 1025685 | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ This table is being used by both GN1 and GN2 from the trait pages! : grep -ir AccessLog *|grep -e "^gn1\|^gn2"|grep \.py|grep -v doc gn1/web/webqtl/showTrait/ShowTraitPage.py: query = "SELECT count(id) FROM AccessLog WHERE ip_address = %s and \ gn1/web/webqtl/showTrait/ShowTraitPage.py: self.cursor.execute("insert into AccessLog(accesstime,ip_address) values(Now(),%s)" ,user_ip) gn1/web/webqtl/textUI/cmdClass.py: query = """SELECT count(id) FROM AccessLog WHERE ip_address = %s AND UNIX_TIMESTAMP()-UNIX_TIMESTAMP(accesstime)<86400""" gn1/web/webqtl/textUI/cmdClass.py: query = """INSERT INTO AccessLog(accesstime,ip_address) values(Now(),%s)""" gn2/wqflask/wqflask/show_trait/show_trait_page.py: query = "SELECT count(id) FROM AccessLog WHERE ip_address = %s and \ gn2/wqflask/wqflask/show_trait/show_trait_page.py: self.cursor.execute("insert into AccessLog(accesstime,ip_address) values(Now(),%s)", user_ip) When looking at the code in GN1 and GN2 it restricts the daily use of the trait data page (set to 1,000 - whoever reaches that?). Unlike mentioned in the schema description, this table does *not* keep track of cookies. From the code it looks like GN2 uses a mixture of Redis and sqlalchemy to keep track of logged in sessions (see gn2/wqflask/wqflask/user_manager.py) and cookies through a user_uuid in model.py. In gn2/wqflask/wqflask/templates/collections/view_anonymous.html it show_trait_page appears to be loaded (need to check). ** AvgMethod Probesetfreeze refers to AvgMethod ** BXDSnPosition Snp table (all snps) Mapping in GN1 shows snps when you select a chromosome. ** CaseAttribute(XRef) Metadata ** CeleralINFO_mm6 ? ** Chr_Length (/cross/BXD.json) Default mm9, column for mm8 select * from Chr_Length; | Name | SpeciesId | OrderId | Length | Length_mm8 | | 1 | 1 | 1 | 197195432 | 197069962 | | 2 | 1 | 2 | 181748087 | 181976762 | Table should be merged with ** Dataset_mbat Menu for BXD (linkouts) ** DatasetMapInvestigator Arthur? ** DataSets Information/metadata ** DatasetStatus Arthur private/public ** DBList and DBType Hooked in API (URL encoding) ** Docs GN2 only (see menu bar) ** Ensembl* Probe information (will be deprecated) ** Genbank Linkout and not important ** GeneCategory Not important. GeneWiki notes function classification. Deprecate. ** GeneChip ** GeneIDXRef Interspecies gene comparison ** GeneList Track info ** Genlist_rn3(3) Rat list ** GeneMap_cuiyan Link outs ** GeneRIF Wiki info (nightly updated from NCBI) XRef should be foreign keys ** Geno (genotype/marker/'marker'.json) SNP or marker info INFO:base.trait:.sql: retrieve_info: select Geno.Chr, Geno.Mb from Geno, Species where Species.Name = 'mouse' and Geno.Name = 'rs3693478' and Geno.SpeciesId = Species.Id | Id | SpeciesId | Name | Marker_Name | Chr | Mb | Sequence | Source | chr_num | Source2 | Comments | used_by_geno_file | Mb_mm8 | Chr_mm8 | | 1 | 1 | 01.001.695 | 01.001.695 | 1 | 4.678288 | GCCCTGCCCACCTCAGAGCAAGCTGCCACCCAGGAGTCCGTGTTTCAGGAGATGTGTGAGGAGGGCCTGCTGGAGGAGTGTGATGGTGAGGATGAGGCAGGCCGTGCCGCG[T/C]AGCCAGAGGCTGGTGATGGGACCACCGAGATCTCACCCACTGGTGCTGCTGATCCTGAGAAGAGGATGGAGAAGAAGACGGAGCAGCAGCACACCGGCGGCGGGAGAAAGCTGCTCGTAAGCTGCTCGTAAGCTACGGGTGCAGCAGGCTGCACTTAGGGCAGCCCGGCTTCAGCACCAAGAACTCTTCAGGCTGCATGGGATCAAGGCCCAGGTGGCCCGAAGGCTGGCAGAACTCGCACACGGGAGGGAGCAGCAGCGCATACAGCGACTGGCAGAGGCTGACAAGCCCCGAAGGCTGGGACGACTCAAGTACCAGGCTCCTGACATTGATGTGCAGCTCAGCTCTGAGCTGTCTGGCCCACTCAGGACACTGAAACCAGAAGGTCACATTCTCCAAGACAGGTTCAAGAGCTTCCAGAAGAGAAATATGATTGAGCCCCGAGAACGAGCCAAGTTCAAGCGCAAATAAAAAATGAAGTTGGTGGAGAAGCGGGCCTACCATGAGATTCAGTTGTAGCTGTGCAGATGTCGGAGCCCCGCCCCTCAATAAAGTTCTGTGACAAAAAAAAAAAAAAAAAAAGAAGAAGAAGAAGAAAAGGAAAAAAAAGAAGAAAAAGAAAAAAAAAGAAAAAAGAAAAAGAAAACACATCACTTGGCAAAACTCCATAGACTCTATGTGATTCATGTTTCAAACATGCACCTA | GNF_SNP | 1 | GNF | NULL | NULL | 4.678288 | 1 | ** GenoCode Belongs to someone else ** GenoData Allele info ** GenoFreeze Big menu (Freeze refers to menu) ** GenoSE SE standard err, not used ** GenoXREF Very important. Key links between Geno, GenoData ** GORef GO terms ** H2 Heritability for probeset(?) ** Homologene Homology, not used much ** InbredSet (/cross/BXD.info) Group in menu ** Indelall, SnpAll, SnpPattern, SnpSource Indel Snp browser (variant browser Gn1) ** Info* Infra system PhP Data Info button Infosystem users has separate entries Also Investigators, User, Organizations, ** LCorrRamin3 Lit. Correlations Prof. Ramin ** Login GN2 login info ** MachineAccessLog Old ** MappingMethod GN1 ** News GN2 ** NStrain pheno publishfreeze (menu) xref (keys) xref links to publish (pubmed), phenotype, pubishdata geno genofreeze xref (keys) xref links to publish (pubmed), genotype, genodata probeset/expr. probesetfreeze xref (keys) xref links to publish (pubmed), probeset, probesetdata probe/expr. probefreeze xref (keys) xref links to publish (pubmed), probe, probedata Each dataset has 3 values (real value (1), number of samples (2), stderr (3)) NStrain = number of phenotype samples ProbesetFreeze contains all data, incl. metabolomic. ** Phenotype This table contains names, full descriptions, and short symbols for traits and phenotype used primarily in the Published Phenotypes databases. Contains 10k rows, March 2016, of which 5000 are for the BXDs). | Id | Pre_publication_description | Post_publication_description | Original_description | Units | Pre_publication_abbreviation | Post_publication_abbreviation | Lab_code | Submitter | Owner | Authorized_Users | +----+-----------------------------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+------------------------------+-------------------------------+----------+-------------+-------+------------------+ | 1 | NULL | Hippocampus weight | Original post publication description: Hippocampus weight | Unknown | NULL | HPCWT | NULL | robwilliams | NULL | robwilliams | | 2 | NULL | Cerebellum weight | Original post publication description: Cerebellum weight | mg | NULL | CBLWT | NULL | robwilliams | NULL | robwilliams | | 3 | NULL | Interleukin 1 activity by peritoneal macrophages stimulated with 10 ug/ml lipopolysaccharide [units/100 ug protein] | Original post publication description: Interleukin 1 activity by peritoneal macrophages stimulated with 10 ug/ml lipopolysaccharide [units/100 ug protein] | units/100 ug protein | NULL | IL1Activity | NULL | robwilliams | NULL | robwilliams | | 4 | NULL | Central nervous system, morphology: Cerebellum weight, whole, bilateral in adults of both sexes [mg] | Original post publication description: Cerebellum weight [mg] | mg | NULL | CBLWT2 | NULL | robwilliams | NULL | robwilliams | | 5 | NULL | The coat color of 79 BXD RI strain | Original post publication description: The coat color of 79 BXD RI strain | Unknown | NULL | CoatColor | NULL | robwilliams | NULL | robwilliams | +----+-----------------------------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+------------------------------+-------------------------------+----------+-------------+-------+------------------+ 5 rows in set (0.00 sec) ** ProbeData Table with fine-grained probe level Affymetrix data only. Contains 1 billion rows March 2016. This table may be deletable since it is only used by the Probe Table display in GN1. Not used in GN2 (double-check). In comparison the "ProbeSetData" table contains more molecular assay data, including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016. In comparison, ProbeData contains data only for Affymetrix probe level data (e.g. Exon array probes and M430 probes). "ProbeData.StrainId" should be "CaseId" or "SampleId". "ProbeData" should probably be "AssayData" or something more neutral. select * from ProbeData limit 2; +--------+----------+---------+ | Id | StrainId | value | +--------+----------+---------+ | 503636 | 42 | 11.6906 | | 503636 | 43 | 11.4205 | +--------+----------+---------+ 2 rows in set (0.00 sec) select count(*) from ProbeData limit 2; +-----------+ | count(*) | +-----------+ | 976753435 | +-----------+ 1 row in set (0.00 sec) ** ProbeSet Comment: PLEASE CHANGE TABLE NAME and rework fields carefully. This is a terrible table but it works well (RWW March 2016). It is used in combination with the crucial TRAIT DATA and ANALYSIS pages in GN1 and GN2. It is also used by annotators using the UPDATE INFO AND DATA web form to correct and update annotation. It is used by Arthur to enter new annotation files and metadata for arrays, genes, proteins, metabolites. The main problem with this table is that it is doing too much work. Initially (2003) this table contained only Affymetrix ProbeSet data for mouse (U74aV2 initially). Many other array platforms for different species were added. At least four other major categories of molecular assays have been added since about 2010. 1. RNA-seq annotation and sequence data for transcripts using ENSEMBL identifiers or NCBI NM_XXXXX and NR_XXXXX type identifiers 2. Protein and peptide annotation and sequence data (see BXD Liver Proteome data, SRM and SWATH type data) with identifiers such as "abcb10_q9ji39_t311" for SRM data and "LLGNMIVIVLGHHLGKDFTPAAQAA" for SWATH data where the latter is just the peptide fragment that has been quantified. Data first entered in 2015 for work by Rudi Aebersold and colleagues. 3. Metabolite annotation and metadata (see BXD Liver Metabolome data) with identifiers that are usually Mass charge ratios such as "149.0970810_MZ" 4. Epigenomic and methylome data (e.g. Human CANDLE Methylation data with identifiers such as "cg24523000") It would make good sense to break this table into four or more types of molecular assay metadata or annotation tables) (AssayRNA_Anno, AssayProtein_Anno, AssayMetabolite_Anno, AssayEpigenome_Anno, AssayMetagenome_Anno), since these assays will have many differences in annotation content compared to RNAs. Some complex logic is used to update contents of this table when annotators modify and correct the information (for example, updating gene symbols). These features requested by Rob so that annotating one gene symbol in one species would annotate all gene symbols in the same species based on common NCBI GeneID number. For example, changing the gene alias for one ProbeSet.Id will changing the list of aliases in all instances with the same gene symbol. If the ProbeSet.BlatSeq (or is this ProbSetTargetSeq) is identical between different ProbeSet.Ids then annotation is forced to be the same even if the symbol or geneID is different. This "feature" was implemented when we found many probe sets with identical sequence but different annotations and identifiers. select count(*) from ProbeSet limit 5; +----------+ | count(*) | +----------+ | 4351030 | +----------+ | Id | ChipId | Name | TargetId | Symbol | description | Chr | Mb | alias | GeneId | GenbankId | SNP | BlatSeq | TargetSeq | UniGeneId | Strand_Probe | Strand_Gene | OMIM | comments | Probe_set_target_region | Probe_set_specificity | Probe_set_BLAT_score | Probe_set_Blat_Mb_start | Probe_set_Blat_Mb_end | Probe_set_strand | Probe_set_Note_by_RW | flag | Symbol_H | description_H | chromosome_H | MB_H | alias_H | GeneId_H | chr_num | name_num | Probe_Target_Description | RefSeq_TranscriptId | Chr_mm8 | Mb_mm8 | Probe_set_Blat_Mb_start_mm8 | Probe_set_Blat_Mb_end_mm8 | HomoloGeneID | Biotype_ENS | ProteinID | ProteinName | Flybase_Id | HMDB_ID | Confidence | ChEBI_ID | ChEMBL_ID | CAS_number | PubChem_ID | ChemSpider_ID | UNII_ID | EC_number | KEGG_ID | Molecular_Weight | Nugowiki_ID | Type | Tissue | PrimaryName | SecondaryNames | PeptideSequence | +------+--------+----------+----------+--------+----------------------------------------------+------+-----------+----------+--------+-----------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------+-------------+--------+----------+-------------------------+-----------------------+----------------------+-------------------------+-----------------------+------------------+----------------------+------+----------+---------------+--------------+------+---------+----------+---------+----------+--------------------------+---------------------+---------+-----------+-----------------------------+---------------------------+--------------+-------------+-----------+-------------+------------+---------+------------+----------+-----------+------------+------------+---------------+---------+-----------+---------+------------------+-------------+------+--------+-------------+----------------+-----------------+ | 7282 | 1 | 93288_at | NULL | Arpc2 | actin related protein 2/3 complex, subunit 2 | 1 | 74.310961 | AK008777 | 76709 | AI835883 | 0 | CCGACTTCCTTAAGGTGCTCAACCGGACTGCTTGCTACTGGATAATCGTGAGGGATTCTCCATTTGGGTTCCATTTTGTACGAGTTTGGCAAATAACCTGCAGAAACGAGCTGTGCTTGCAAGGACTTGATAGTTCCTAATCCTTTTCCAAGCTGTTTGCTTTGCAATATGT | ccgacttccttaaggtgctcaaccgtnnnnnnccnannnnccnagaaaaaagaaatgaaaannnnnnnnnnnnnnnnnnnttcatcccgctaactcttgggaactgaggaggaagcgctgtcgaccgaagnntggactgcttgctactggataatcgtnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnntgagggattctccatttgggttccattttgtacgagtttggcaaataacctgcagaaacgagctgtgcttgcaaggacttgatagttcctaagaattanaanaaaaaaaanaanttccacttgatcaanttaattcccttttatttttcctccctcantccccttccttttccaagctgtttgctttgcaatatgt | Mm.337038 | + | | 604224 | | NULL | 8.45 | 169 | 74.310961 | 74.31466 | NULL | NULL | 3 | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 93288 | NULL | XM_129773 | 1 | 74.197594 | 74.197594 | 74.201293 | 4187 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+--------+----------+----------+--------+----------------------------------------------+------+-----------+----------+--------+-----------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------+-------------+--------+----------+-------------------------+-----------------------+----------------------+-------------------------+-----------------------+------------------+----------------------+------+----------+---------------+--------------+------+---------+----------+---------+----------+--------------------------+---------------------+---------+-----------+-----------------------------+---------------------------+--------------+-------------+-----------+-------------+------------+---------+------------+----------+-----------+------------+------------+---------------+---------+-----------+---------+------------------+-------------+------+--------+-------------+----------------+-----------------+ 2 rows in set (0.00 sec) ** ProbeSetXRef (phenotypes/dataset_name.json) For every probe set (read dataset measuring point): select * from ProbeSetXRef; | ProbeSetFreezeId | ProbeSetId | DataId | Locus_old | LRS_old | pValue_old | mean | se | Locus | LRS | pValue | additive | h2 | | 112 | 123528 | 23439389 | NULL | NULL | NULL | 6.7460707070707 | NULL | rs6239372 | 10.9675593568894 | 0.567 | 0.0448545966228878 | NULL | | 112 | 123527 | 23439388 | NULL | NULL | NULL | 6.19416161616162 | NULL | rs13476936 | 10.9075670392762 | 0.567 | -0.0358456732993988 | NULL | where ProbeSetFreezeId is the dataset (experiment). ProbesetId refers to the probe set information (measuring point). DataId points to the data point. The othe values are used for search. It is used in search thus: SELECT distinct ProbeSet.Name as TNAME, ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS, ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM, ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL, ProbeSet.name_num as TNAME_NUM FROM ProbeSetXRef, ProbeSet WHERE ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = 112 ORDER BY ProbeSet.symbol ASC limit 5; | TNAME | TMEAN | TLRS | TPVALUE | TCHR_NUM | TMB | TSYMBOL | TNAME_NUM | | 1445618_at | 7.05679797979798 | 13.5417452764616 | 0.17 | 8 | 75.077895 | NULL | 1445618 | | 1452452_at | 7.232 | 30.4944361132252 | 0.0000609756097560421 | 12 | 12.6694 | NULL | 1452452 | ** ProbeSetData Probedata - main molecular data. Probesets, metabolome, Almost all important molecular assay data is in this table including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016. In comparison, ProbeData contains data only for Affymetrix probe level data (e.g. Exon array probes and M430 probes). select count(*) from ProbeSetData limit 5; +---------------+ | count(*) | +---------------+ | 2,510,566,472 | +---------------+ select * from ProbeSetData limit 5; +----+----------+-------+ | Id | StrainId | value | +----+----------+-------+ | 1 | 1 | 5.742 | | 1 | 2 | 5.006 | | 1 | 3 | 6.079 | | 1 | 4 | 6.414 | | 1 | 5 | 4.885 | +----+----------+-------+ show indexes from ProbeSetData; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | ProbeSetData | 0 | DataId | 1 | Id | A | 34868978 | NULL | NULL | | BTREE | | | | ProbeSetData | 0 | DataId | 2 | StrainId | A | 2510566472 | NULL | NULL | | BTREE | | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ select * from Strain limit 5; +----+----------+----------+-----------+--------+-------+ | Id | Name | Name2 | SpeciesId | Symbol | Alias | +----+----------+----------+-----------+--------+-------+ | 1 | B6D2F1 | B6D2F1 | 1 | NULL | NULL | | 2 | C57BL/6J | C57BL/6J | 1 | B6J | NULL | | 3 | DBA/2J | DBA/2J | 1 | D2J | NULL | | 4 | BXD1 | BXD1 | 1 | NULL | NULL | | 5 | BXD2 | BXD2 | 1 | NULL | NULL | +----+----------+----------+-----------+--------+-------+ show indexes from Strain; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Strain | 0 | PRIMARY | 1 | Id | A | 14368 | NULL | NULL | | BTREE | | | | Strain | 0 | Name | 1 | Name | A | 14368 | NULL | NULL | YES | BTREE | | | | Strain | 0 | Name | 2 | SpeciesId | A | 14368 | NULL | NULL | | BTREE | | | | Strain | 1 | Symbol | 1 | Symbol | A | 14368 | NULL | NULL | YES | BTREE | | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ A typical query may look like SELECT Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id FROM (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef) left join ProbeSetSE on (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId) WHERE ProbeSetFreeze.name = 'B139_K_1206_M' AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetXRef.DataId = ProbeSetData.Id AND ProbeSetData.StrainId = Strain.Id Order BY Strain.Name +-------+-------+-------+----------+ | Name | value | error | Id | +-------+-------+-------+----------+ | SM001 | 38.3 | NULL | 25309550 | | SM001 | 2.7 | NULL | 25309520 | | SM001 | 20.3 | NULL | 25309507 | | SM001 | 125.8 | NULL | 25309511 | | SM001 | 8.2 | NULL | 25309534 | +-------+-------+-------+----------+ 5 rows in set (22.28 sec) select * from ProbeSetFreeze limit 5; +----+---------------+-------+-------------+---------------------------------+---------------------------------------------+-------------------------+------------+-----------+--------+-----------------+-----------------+-----------+ | Id | ProbeFreezeId | AvgID | Name | Name2 | FullName | ShortName | CreateTime | OrderList | public | confidentiality | AuthorisedUsers | DataScale | +----+---------------+-------+-------------+---------------------------------+---------------------------------------------+-------------------------+------------+-----------+--------+-----------------+-----------------+-----------+ | 1 | 3 | 1 | Br_U_0803_M | BXDMicroArray_ProbeSet_August03 | UTHSC Brain mRNA U74Av2 (Aug03) MAS5 | Brain U74Av2 08/03 MAS5 | 2003-08-01 | NULL | 0 | 0 | NULL | log2 | | 2 | 10 | 1 | Br_U_0603_M | BXDMicroArray_ProbeSet_June03 | UTHSC Brain mRNA U74Av2 (Jun03) MAS5 | Brain U74Av2 06/03 MAS5 | 2003-06-01 | NULL | 0 | 0 | NULL | log2 | | 3 | 8 | 1 | Br_U_0303_M | BXDMicroArray_ProbeSet_March03 | UTHSC Brain mRNA U74Av2 (Mar03) MAS5 | Brain U74Av2 03/03 MAS5 | 2003-03-01 | NULL | 0 | 0 | NULL | log2 | | 4 | 5 | 1 | Br_U_0503_M | BXDMicroArray_ProbeSet_May03 | UTHSC Brain mRNA U74Av2 (May03) MAS5 | Brain U74Av2 05/03 MAS5 | 2003-05-01 | NULL | 0 | 0 | NULL | log2 | | 5 | 4 | 1 | HC_U_0303_M | GNFMicroArray_ProbeSet_March03 | GNF Hematopoietic Cells U74Av2 (Mar03) MAS5 | GNF U74Av2 03/03 MAS5 | 2003-03-01 | NULL | 0 | 0 | NULL | log2 | +----+---------------+-------+-------------+---------------------------------+---------------------------------------------+-------------------------+------------+-----------+--------+-----------------+-----------------+-----------+ select * from ProbeSetXRef limit 5; +------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+-----------------+--------------------+--------+----------------------+------+ | ProbeSetFreezeId | ProbeSetId | DataId | Locus_old | LRS_old | pValue_old | mean | se | Locus | LRS | pValue | additive | h2 | +------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+-----------------+--------------------+--------+----------------------+------+ | 1 | 1 | 1 | 10.095.400 | 13.3971627898894 | 0.163 | 5.48794285714286 | 0.08525787814808819 | rs13480619 | 12.590069931048001 | 0.269 | -0.28515625 | NULL | | 1 | 2 | 2 | D15Mit189 | 10.042057464356201 | 0.431 | 9.90165714285714 | 0.0374686634976217 | CEL-17_50896182 | 10.5970737900941 | 0.304 | -0.11678333333333299 | NULL | | 1 | 3 | 3 | D5Mit139 | 5.43678531742749 | 0.993 | 7.83948571428571 | 0.0457583416912569 | rs13478499 | 6.0970532702754 | 0.988 | 0.112957489878542 | NULL | | 1 | 4 | 4 | D1Mit511 | 9.87815279480766 | 0.483 | 8.315628571428569 | 0.0470396593931327 | rs6154379 | 11.774867551173099 | 0.286 | -0.157113725490196 | NULL | | 1 | 5 | 5 | D16H21S16 | 10.191723834264499 | 0.528 | 9.19345714285714 | 0.0354801718293322 | rs4199265 | 10.923263374016202 | 0.468 | 0.11476470588235299 | NULL | +------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+-----------------+--------------------+--------+----------------------+------+ Note that the following unlimited search is very slow: select max(value) from ProbeSetData; +------------+ | max(value) | +------------+ | 26436006 | +------------+ 1 row in set (2 min 16.31 sec) which is in some form is used in the search page, see [[https://github.com/genenetwork/genenetwork2_diet/blob/master/wqflask/wqflask/do_search.py#L811][the search code]]. *** Improvements? Suggestions on the schema page: "StrainId" should be "CaseId" or "SampleId". "ProbeSetData" should probably be "AssayData" or something more neutral. *** Comments I think the ProbeSetData table should be generalized to a 'phenotypes' table with an 'sample_id' column and a 'value' column. A new table 'samples' will link each sample against an 'experiment', an 'individual' and which in turn can link to a 'strain'. Experiment is here in a wide sense, GTex can be one - I don't want to use dataset ;) This means a (slight) reordering: phenotypes: (id), sample_id, value samples: experiment_id, individual_id experiments: name, version individual: strain_id strains: species_id species: ... ProbeData is also interesting, because it has the same structure as ProbeSetData, but only contains microarrays. This tables should be one (when we clear up the cross-referencing) as they both contain phenotype values. Both are large tables. PublishData is another phenotype table with values only which can be merged into that same table. So we have phenotype data in 3 tables with exactly the same layout. There is also TissueProbeSet*, but we'll ignore those for now. I think we should merge these into one and have the sample ref refer to the type of data (probeset, probe, metabolomics, whatever). These are all phenotype values and by having them split into different tables they won't play well when looking for correlations. ProbeSet contains the metadata on the probes and should (eventually) move into NoSQL. There is plenty redundancy in that table now. I know it is going to be a pain to reorganize the database, but if we want to use it in the long run we are going to have to simplify it. ** ProbeSetFreeze and ProbeFreeze (/dataset/name.json) GN_SERVER: /dataset/HC_M2_0606_P.json ProbesetFreeze contains DataSet information, such as name, fullname of datasets, as well as whether they are public and how the data is scaled: select * from ProbeSetFreeze; | Id | ProbeFreezeId | AvgID | Name | Name2 | FullName | ShortName | CreateTime | OrderList | public | confidentiality | AuthorisedUsers | DataScale | | 112 | 30 | 2 | HC_M2_0606_P | Hippocampus_M430_V2_BXD_PDNN_Jun06 | Hippocampus Consortium M430v2 (Jun06) PDNN | Hippocampus M430v2 BXD 06/06 PDNN | 2006-06-23 | NULL | 2 | 0 | NULL | log2 | Another table contains a tissue reference and a back reference to the cross type: select * from ProbeFreeze; | Id | ProbeFreezeId | ChipId | TissueId | Name | FullName | ShortName | CreateTime | InbredSetId | | 30 | 30 | 4 | 9 | Hippocampus Consortium M430v2 Probe (Jun06) | | | 2006-07-07 | 1 | NOTE: these tables can probably be merged into one. show indexes from ProbeSetFreeze; +----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 2 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 2 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 0 | Name | 1 | Name | A | 2 | NULL | NULL | YES | BTREE | | | | ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 2 | NULL | NULL | | BTREE | | | +----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ ** ProbeSetSE select * from ProbeSetSE limit 5; +--------+----------+----------+ | DataId | StrainId | error | +--------+----------+----------+ | 1 | 1 | 0.681091 | | 1 | 2 | 0.361151 | | 1 | 3 | 0.364342 | | 1 | 4 | 0.827588 | | 1 | 5 | 0.303492 | +--------+----------+----------+ ** Publication Publication: | Id | PubMed_ID | Abstract | Title | Pages | Month | Year | ** Publishdata (all pheno) One of three phenotype tables. mysql> select * from PublishData limit 5; +---------+----------+-------+ | Id | StrainId | value | +---------+----------+-------+ | 8966353 | 349 | 29.6 | | 8966353 | 350 | 27.8 | | 8966353 | 351 | 26.6 | | 8966353 | 352 | 28.5 | | 8966353 | 353 | 24.6 | +---------+----------+-------+ 5 rows in set (0.25 sec) See below for phenotype access. ** QuickSearch No longer used ** role empty ** Sample* No longer used ** Species & Strain (should be sample) select * from Species; +----+-----------+----------------------+----------------+----------------------+-------------------------+------------+---------+ | Id | SpeciesId | SpeciesName | Name | MenuName | FullName | TaxonomyId | OrderId | +----+-----------+----------------------+----------------+----------------------+-------------------------+------------+---------+ | 1 | 1 | Mouse | mouse | Mouse | Mus musculus | 10090 | 30 | | 2 | 2 | Rat | rat | Rat | Rattus norvegicus | 10116 | 40 | | 3 | 3 | Arabidopsis thaliana | arabidopsis | Arabidopsis thaliana | Arabidopsis thaliana | 3702 | 60 | | 4 | 4 | Human | human | Human | Homo sapiens | 9606 | 10 | | 5 | 5 | Barley | barley | Barley | Hordeum vulgare | 4513 | 70 | | 6 | 6 | Drosophila | drosophila | Drosophila | Drosophila melanogaster | 7227 | 50 | | 7 | 7 | Macaque monkey | macaque monkey | Macaque monkey | Macaca mulatta | 9544 | 20 | | 8 | 8 | Soybean | soybean | Soybean | Soybean | 3847 | 80 | | 9 | 9 | Tomato | tomato | Tomato | Tomato | 4081 | 90 | +----+-----------+----------------------+----------------+----------------------+-------------------------+------------+---------+ ** InbredSet Menu ** TableComments Metadata on DB ** Temp* User upload data ** Tissue Menu - 3rd level ** TissueP* Correlation tables ** User collection User selection - retained ** UserPrivilege ** Vlookup * Fetching Data ** Search What happens at the main search menu can be emulated with curl. *** Search Mouse mRNA from HC_M2_0606_P dataset : curl "http://localhost:5003/search?species=mouse&group=BXD&ppocampus+mRNA&dataset=HC_M2_0606_P&search_terms_or=&search_terms_and=MEAN%3D%2815+16%29+LRS%3D%2823+46%29+&FormID=searchResult" the first SQL query checks whether a dataset is confidential or not ayd fetches full name info INFO:base.data_set:.sql: geno_mrna_confidentiality: u'SELECT Id, Name, FullName, confidentiality,AuthorisedUsers FROM ProbeSetFreeze WHERE Name = "HC_M2_0606_P"' The following is executed once per search: INFO:db.call:.sql: retrieve_other_names: SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, ProbeSetFreeze.DataScale, Tissue.Name FROM ProbeSetFreeze, ProbeFreeze, Tissue WHERE ProbeSetFreeze.public > 0 AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND ProbeFreeze.TissueId = Tissue.Id AND (ProbeSetFreeze.Name = 'HC_M2_0606_P' OR ProbeSetFreeze.FullName = 'HC_M2_0606_P' OR ProbeSetFreeze.ShortName = 'HC_M2_0606_P') INFO:db.call:.sql: (112, 'HC_M2_0606_P', 'Hippocampus Consortium M430v2 (Jun06) PDNN', 'Hippocampus M430v2 BXD 06/06 PDNN', 'log2', 'Hippocampus mRNA') INFO:db.call:.sql: __init__: SELECT InbredSet.Name, InbredSet.Id FROM InbredSet, ProbeSetFreeze, ProbeFreeze WHERE ProbeFreeze.InbredSetId = InbredSet.Id AND ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId AND ProbeSetFreeze.Name = "HC_M2_0606_P" INFO:db.call:.sql: ('BXD', 1) The actual search is SELECT distinct ProbeSet.Name as TNAME, 0 as thistable, ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS, ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM, ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL, ProbeSet.name_num as TNAME_NUM FROM ProbeSetXRef, ProbeSet WHERE ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = 112 ORDER BY ProbeSet.symbol ASC limit 5; INFO:base.species:.sql: __init__: Select Chr_Length.Name, Chr_Length.OrderId, Length from Chr_Length, InbredSet where Chr_Length.SpeciesId = InbredSet.SpeciesId AND InbredSet.Name = 'BXD' Order by OrderId And the following are executed for every trait: INFO:wqflask.do_search:.sql: execute: SELECT distinct ProbeSet.Name as TNAME, 0 as thistable, ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS, ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM, ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL, ProbeSet.name_num as TNAME_NUM FROM ProbeSetXRef, ProbeSet WHERE (( ProbeSetXRef.mean > 15.0 and ProbeSetXRef.mean < 16.0 )AND( ProbeSetXRef.LRS > 23.0 and ProbeSetXRef.LRS < 46.0 )) and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = 112 ORDER BY ProbeSet.symbol ASC INFO:base.trait:.sql: retrieve_info: SELECT ProbeSet.name, ProbeSet.symbol, ProbeSet.description, ProbeSet.probe_target_description, ProbeSet.chr, ProbeSet.mb, ProbeSet.alias, ProbeSet.geneid, ProbeSet.genbankid, ProbeSet.unigeneid, ProbeSet.omim, ProbeSet.refseq_transcriptid, ProbeSet.blatseq, ProbeSet.targetseq, ProbeSet.chipid, ProbeSet.comments, ProbeSet.strand_probe, ProbeSet.strand_gene, ProbeSet.probe_set_target_region, ProbeSet.probe_set_specificity, ProbeSet.probe_set_blat_score, ProbeSet.probe_set_blat_mb_start, ProbeSet.probe_set_blat_mb_end, ProbeSet.probe_set_strand, ProbeSet.probe_set_note_by_rw, ProbeSet.flag FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetFreeze.Name = 'HC_M2_0606_P' AND ProbeSet.Name = '1443823_s_at' INFO:base.trait:.sql: retrieve_info: SELECT HomologeneId FROM Homologene, Species, InbredSet WHERE Homologene.GeneId =98660 AND InbredSet.Name = 'BXD' AND InbredSet.SpeciesId = Species.Id AND Species.TaxonomyId = Homologene.TaxonomyId INFO:base.trait:.sql: retrieve_info: SELECT ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.mean, ProbeSetXRef.additive FROM ProbeSetXRef, ProbeSet WHERE ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSet.Name = "1443823_s_at" AND ProbeSetXRef.ProbeSetFreezeId =112 INFO:base.trait:.sql: retrieve_info: select Geno.Chr, Geno.Mb from Geno, Species where Species.Name = 'mouse' and Geno.Name = 'NES13033186' and Geno.SpeciesId = Species.Id and finally mean and chromosome positions are fetched a second time for every trait with INFO:base.data_set:.sql: get_trait_info: select ProbeSetXRef.mean from ProbeSetXRef, ProbeSet where ProbeSetXRef.ProbeSetFreezeId = 112 and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSet.Name = '1443823_s_at' INFO:base.data_set:.sql: get_trait_info: select Geno.Chr, Geno.Mb from Geno, Species where Species.Name = 'mouse' and Geno.Name = 'NES13033186' and Geno.SpeciesId = Species.Id (that is a bug!). ** Fetch phenotype information *** Through the trait page When hitting the trait page, e.g. curl "http://localhost:5003/show_trait?trait_id=1443823_s_aet=HC_M2_0606_P" First the BXD's are queried with DEBUG:base.data_set:.get_samplelist: Sample list: : ['BXD1', 'BXD2', 'BXD5', ... main probeset info (trait) is retrieved with SELECT ProbeSet.name, ProbeSet.symbol, ProbeSet.description, ProbeSet.probe_target_description, ProbeSet.chr, ProbeSet.mb, ProbeSet.alias, ProbeSet.geneid, ProbeSet.genbankid, ProbeSet.unigeneid, ProbeSet.omim, ProbeSet.refseq_transcriptid, ProbeSet.blatseq, ProbeSet.targetseq, ProbeSet.chipid, ProbeSet.comments, ProbeSet.strand_probe, ProbeSet.strand_gene, ProbeSet.probe_set_target_region, ProbeSet.probe_set_specificity, ProbeSet.probe_set_blat_score, ProbeSet.probe_set_blat_mb_start, ProbeSet.probe_set_blat_mb_end, ProbeSet.probe_set_strand, ProbeSet.probe_set_note_by_rw, ProbeSet.flag FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetFreeze.Name = 'HC_M2_0606_P' AND ProbeSet.Name = '1443823_s_at' Followed by INFO:base.trait:.sql: retrieve_info: SELECT ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.mean, ProbeSetXRef.additive FROM ProbeSetXRef, ProbeSet WHERE ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSet.Name = "1443823_s_at" AND ProbeSetXRef.ProbeSetFreezeId =112 | Locus | LRS | pValue | mean | additive | | NES13033186 | 35.466324074542 | 0.00000900000000003676 | 15.0551313131313 | -0.16750405405405402 | Then the interesting bit, the sample data is fetched with INFO:base.data_set:.sql: retrieve_sample_data: SELECT Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id, Strain.Name2 FROM (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef) left join ProbeSetSE on (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId) WHERE ProbeSet.Name = '1443823_s_at' AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetFreeze.Name = 'HC_M2_0606_P' AND ProbeSetXRef.DataId = ProbeSetData.Id AND ProbeSetData.StrainId = Strain.Id Order BY Strain.Name | Name | value | error | Id | Name2 | | 129S1/SvImJ | 14.552 | NULL | 23422417 | 129S1/SvImJ | | A/J | 14.34 | NULL | 23422417 | A/J | | AKR/J | 14.338 | NULL | 23422417 | AKR/J | | B6D2F1 | 15.251 | NULL | 23422417 | B6D2F1 | | BALB/cByJ | 14.164 | NULL | 23422417 | BALB/cByJ | | BALB/cJ | 14.563 | NULL | 23422417 | BALB/cJ | | BXD1 | 15.198 | NULL | 23422417 | BXD1 | | BXD11 | 15.084 | NULL | 23422417 | BXD11 | | BXD12 | 15.192 | NULL | 23422417 | BXD12 | etc. Then some repeated queries and this fetchest the nearest SNP INFO:wqflask.show_trait.show_trait:.sql: get_nearest_marker: SELECT Geno.Name FROM Geno, GenoXRef, GenoFreeze WHERE Geno.Chr = '1' AND GenoXRef.GenoId = Geno.Id AND GenoFreeze.Id = GenoXRef.GenoFreezeId AND GenoFreeze.Name = 'BXDGeno' ORDER BY ABS( Geno.Mb - 173.149434) limit 1; | Name | | NES13033186 | *** Digging deeper To get at phenotype data ProbeSetData is the main table (almost all important molecular assay data is in this table including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016) select count(*) from ProbeSetData limit 5; +---------------+ | count(*) | +---------------+ | 2,510,566,472 | +---------------+ select * from ProbeSetData limit 5; +----+----------+-------+ | Id | StrainId | value | +----+----------+-------+ | 1 | 1 | 5.742 | | 1 | 2 | 5.006 | | 1 | 3 | 6.079 | | 1 | 4 | 6.414 | | 1 | 5 | 4.885 | +----+----------+-------+ This table is used in : wqflask/base/do_search.py : wqflask/base/data_set.py : wqflask/utility/AJAX_table.py : wqflask/wqflask/correlation/show_corr_results.py In there we find 'ProbeSetData.Id = ProbeSetXRef.dataId'. select * from ProbeSetXRef limit 5; +------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+-----------------+--------------------+--------+----------------------+------+ | ProbeSetFreezeId | ProbeSetId | DataId | Locus_old | LRS_old | pValue_old | mean | se | Locus | LRS | pValue | additive | h2 | +------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+-----------------+--------------------+--------+----------------------+------+ | 1 | 1 | 1 | 10.095.400 | 13.3971627898894 | 0.163 | 5.48794285714286 | 0.08525787814808819 | rs13480619 | 12.590069931048001 | 0.269 | -0.28515625 | NULL | | 1 | 2 | 2 | D15Mit189 | 10.042057464356201 | 0.431 | 9.90165714285714 | 0.0374686634976217 | CEL-17_50896182 | 10.5970737900941 | 0.304 | -0.11678333333333299 | NULL | | 1 | 3 | 3 | D5Mit139 | 5.43678531742749 | 0.993 | 7.83948571428571 | 0.0457583416912569 | rs13478499 | 6.0970532702754 | 0.988 | 0.112957489878542 | NULL | | 1 | 4 | 4 | D1Mit511 | 9.87815279480766 | 0.483 | 8.315628571428569 | 0.0470396593931327 | rs6154379 | 11.774867551173099 | 0.286 | -0.157113725490196 | NULL | | 1 | 5 | 5 | D16H21S16 | 10.191723834264499 | 0.528 | 9.19345714285714 | 0.0354801718293322 | rs4199265 | 10.923263374016202 | 0.468 | 0.11476470588235299 | NULL | +------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+-----------------+--------------------+--------+----------------------+------+ i.e., for Strain Id 1 (DataId) 1, the locus '10.095.400' has a phenotype value of 5.742. Interestingly ProbeData and PublishData have the same layout as ProbeSetData. ProbeData is only in use for Affy assays - and not used for computations. PublishData contains trait values. ProbeSetData.id matches ProbeSetXRef.DataId while PublishData.id matches PublishXRef.DataId. select * from PublishXRef limit 3; +-------+-------------+-------------+---------------+---------+----------------+------------------+-----------+----------+-------------------------------------------------------+ | Id | InbredSetId | PhenotypeId | PublicationId | DataId | Locus | LRS | additive | Sequence | comments | +-------+-------------+-------------+---------------+---------+----------------+------------------+-----------+----------+-------------------------------------------------------+ | 10001 | 8 | 1 | 1 | 8966353 | D2Mit5 | 10.18351644706 | -1.20875 | 1 | | | 10001 | 7 | 2 | 53 | 8966813 | D7Mit25UT | 9.85534330983917 | -2.86875 | 1 | | | 10001 | 4 | 3 | 81 | 8966947 | CEL-6_57082524 | 11.7119505898121 | -23.28875 | 1 | elissa modified Abstract at Tue Jun 7 11:38:00 2005 | +-------+-------------+-------------+---------------+---------+----------------+------------------+-----------+----------+-------------------------------------------------------+ 3 rows in set (0.00 sec) ties the trait data (PublishData) with the inbredsetid (matching PublishFreeze.InbredSetId), locus and publication. select * from PublishFreeze -> ; +----+------------+--------------------------+-------------+------------+--------+-------------+-----------------+-----------------+ | Id | Name | FullName | ShortName | CreateTime | public | InbredSetId | confidentiality | AuthorisedUsers | +----+------------+--------------------------+-------------+------------+--------+-------------+-----------------+-----------------+ | 1 | BXDPublish | BXD Published Phenotypes | BXDPublish | 2004-07-17 | 2 | 1 | 0 | NULL | | 18 | HLCPublish | HLC Published Phenotypes | HLC Publish | 2012-02-20 | 2 | 34 | 0 | NULL | +----+------------+--------------------------+-------------+------------+--------+-------------+-----------------+-----------------+ 2 rows in set (0.02 sec) which gives us the datasets. GeneNetwork1 has a limited REST interface, if you do : curl "http://robot.genenetwork.org/webqtl/main.py?cmd=get&probeset=1443823_s_at&db=HC_M2_0606_P" we get : ProbeSetID B6D2F1 C57BL/6J DBA/2J BXD1 BXD2 BXD5 BXD6 BXD8 BXD9 BXD11 BXD12 BXD13 BXD15 BXD16 BXD19 BXD20 BXD21 BXD22 BXD23 BXD24 BXD27 BXD28 BXD29 BXD31 BXD32 BXD33 BXD34 BXD38 BXD39 BXD40 BXD42 BXD67 BXD68 BXD43 BXD44 BXD45 BXD48 BXD50 BXD51 BXD55 BXD60 BXD61 BXD62 BXD63 BXD64 BXD65 BXD66 BXD69 BXD70 BXD73 BXD74 BXD75 BXD76 BXD77 BXD79 BXD73a BXD83 BXD84 BXD85 BXD86 BXD87 BXD89 BXD90 BXD65b BXD93 BXD94 A/J AKR/J C3H/HeJ C57BL/6ByJ CXB1 CXB2 CXB3 CXB4 CXB5 CXB6 CXB7 CXB8 CXB9 CXB10 CXB11 CXB12 CXB13 BXD48a 129S1/SvImJ BALB/cJ BALB/cByJ LG/J NOD/ShiLtJ PWD/PhJ BXD65a BXD98 BXD99 CAST/EiJ KK/HlJ WSB/EiJ NZO/HlLtJ PWK/PhJ D2B6F1 : 1443823_s_at 15.251 15.626 14.716 15.198 14.918 15.057 15.232 14.968 14.87 15.084 15.192 14.924 15.343 15.226 15.364 15.36 14.792 14.908 15.344 14.948 15.08 15.021 15.176 15.14 14.796 15.443 14.636 14.921 15.22 15.62 14.816 15.39 15.428 14.982 15.05 15.13 14.722 14.636 15.242 15.527 14.825 14.416 15.125 15.362 15.226 15.176 15.328 14.895 15.141 15.634 14.922 14.764 15.122 15.448 15.398 15.089 14.765 15.234 15.302 14.774 14.979 15.212 15.29 15.012 15.041 15.448 14.34 14.338 14.809 15.046 14.816 15.232 14.933 15.255 15.21 14.766 14.8 15.506 15.749 15.274 15.599 15.673 14.651 14.692 14.552 14.563 14.164 14.546 15.044 14.695 15.162 14.772 14.645 15.493 14.75 14.786 15.003 15.148 15.221 (see https://github.com/genenetwork/gn_server/blob/master/doc/API.md for the latest REST API). getTraitData is defined in the file [[https://github.com/genenetwork/genenetwork/blob/master/web/webqtl/textUI/cmdClass.py#L134][web/webqtl/textUI/cmdClass.py]]. probe is None, so the code at line 199 is run query = "SELECT Strain.Name, %sData.value from %sData, Strain, %s, %sXRef WHERE %s.Name = '%s' and %sXRef.%sId = %s.Id and %sXRef.%sFreezeId = %d and %sXRef.DataId = %sData.Id and %sData.StrainId = Strain.Id order by Strain.Id" % (prefix, prefix, prefix, prefix, prefix, probeset,prefix, prefix, prefix, prefix, prefix, dbId, prefix, prefix, prefix) where prefix is ProbeSet (one presumes). So, let's see if we can do this by hand SELECT Strain.Name, ProbeSetData.value from ProbeSetData, Strain, ProbeSet, ProbeSetXRef WHERE ProbeSet.Name = '1443823_s_at' and ProbeSetXRef.ProbeSetId = ProbeSet.Id and ProbeSetXRef.ProbeSetFreezeId = $dbid and ProbeSetXRef.DataId = ProbeSetData.Id and ProbeSetData.StrainId = Strain.Id ORDER BY Strain.Id The $dbid is listed in the ProbeSetFreeze table, SELECT ProbeFreezeId,Name FROM ProbeSetFreeze WHERE Name='HC_M2_0606_P' limit 5; +---------------+--------------+ | ProbeFreezeId | Name | +---------------+--------------+ | 30 | HC_M2_0606_P | +---------------+--------------+ select id,name from ProbeSet WHERE Name = '1443823_s_at' limit 5; +--------+--------------+ | id | name | +--------+--------------+ | 106556 | 1443823_s_at | +--------+--------------+ So query = "SELECT Strain.Name, %sData.value from %sData, Strain, %s, %sXRef WHERE %s.Name = '%s' and %sXRef.%sId = %s.Id and %sXRef.%sFreezeId = %d and %sXRef.DataId = %sData.Id and %sData.StrainId = Strain.Id order by Strain.Id" % (prefix, prefix, prefix, prefix, prefix, probeset,prefix, prefix, prefix, prefix, prefix, dbId, prefix, prefix, prefix) CORRECT NAME: SELECT Strain.Name, ProbeSetData.value from ProbeSetData, Strain, ProbeSet, ProbeSetXRef WHERE ProbeSet.Name = 'at_probe' and ProbeSetXRef.ProbeSetId = ProbeSet.Id and ProbeSetXRef.ProbeSetFreezeId = dbid and ProbeSetXRef.DataId = ProbeSetData.Id and ProbeSetData.StrainId = Strain.Id order by Strain.Id select * from ProbeSetXRef WHERE probesetfreezeid=30 limit 5; +------------------+------------+--------+------------+--------------------+------------+--------------------+--------------------+-----------------+--------------------+--------+-------------------+------+ | ProbeSetFreezeId | ProbeSetId | DataId | Locus_old | LRS_old | pValue_old | mean | se | Locus | LRS | pValue | additive | h2 | +------------------+------------+--------+------------+--------------------+------------+--------------------+--------------------+-----------------+--------------------+--------+-------------------+------+ | 30 | 1 | 445962 | 01.059.350 | 7.1753152078069595 | 0.961 | 30.0646 | 1.79763935596594 | rs13475891 | 7.1753152078069204 | 0.973 | 4.71778125 | NULL | | 30 | 2 | 445963 | D4Mit156 | 7.58944292943285 | 0.724 | 232.38328571428602 | 9.00278909374791 | CEL-4_118751423 | 7.57513435426218 | 0.793 | 25.2660951417004 | NULL | | 30 | 3 | 445964 | D1Mit134 | 9.766065497826819 | 0.737 | 47.206 | 1.58413526287766 | mCV23431007 | 9.76606549782677 | 0.797 | -4.82405952380952 | NULL | | 30 | 4 | 445965 | D1Mit155 | 18.0045829157241 | 0.033 | 132.29248571428602 | 4.37799472291842 | rs3689947 | 17.9365068406286 | 0.049 | -16.945619047619 | NULL | | 30 | 5 | 445966 | D5Mit197 | 9.51068902627823 | 0.476 | 271.309971428571 | 7.4294268316065395 | rs6239372 | 10.4214974316601 | 0.41 | -25.6148045454546 | NULL | +------------------+------------+--------+------------+--------------------+------------+--------------------+--------------------+-----------------+--------------------+--------+-------------------+------+ So, apparently ProbeSetFreezeID points to the database identifier in ProbeSetFreeze which has the name of the 'DB'. OK, that kinda makes sense now. Meanwhile Probeset.name points to the phenotype name. ProbeSetXRef binds these tables together. Finally there is the data in select * from ProbeSetData limit 5; +----+----------+-------+ | Id | StrainId | value | +----+----------+-------+ | 1 | 1 | 5.742 | | 1 | 2 | 5.006 | | 1 | 3 | 6.079 | | 1 | 4 | 6.414 | | 1 | 5 | 4.885 | +----+----------+-------+ 5 rows in set (0.00 sec) linked by ProbeSetXRef.dataid. *** For PublishData: List datasets for BXD (InbredSetId=1): select * from PublishXRef where InbredSetId=1 limit 3; +-------+-------------+-------------+---------------+---------+-----------+------------------+------------------+----------+--------------------------------------------------------------------------------+ | Id | InbredSetId | PhenotypeId | PublicationId | DataId | Locus | LRS | additive | Sequence | comments | +-------+-------------+-------------+---------------+---------+-----------+------------------+------------------+----------+--------------------------------------------------------------------------------+ | 10001 | 1 | 4 | 116 | 8967043 | rs8253516 | 13.4974914158039 | 2.39444444444444 | 1 | robwilliams modified post_publication_description at Mon Jul 30 14:58:10 2012 | | 10002 | 1 | 10 | 116 | 8967044 | rs3666069 | 22.0042692151629 | 2.08178571428572 | 1 | robwilliams modified phenotype at Thu Oct 28 21:43:28 2010 | | 10003 | 1 | 15 | 116 | 8967045 | D18Mit4 | 15.5929163293343 | 19.0882352941176 | 1 | robwilliams modified phenotype at Mon May 23 20:52:19 2011 | +-------+-------------+-------------+---------------+---------+-----------+------------------+------------------+----------+--------------------------------------------------------------------------------+ where ID is the 'record' or, effectively, dataset. select distinct(publicationid) from PublishXRef where InbredSetId=1 limit 3; +---------------+ | publicationid | +---------------+ | 116 | | 117 | | 118 | +---------------+ select distinct PublishXRef.id,publicationid,phenotypeid,Phenotype.post_publication_description from PublishXRef,Phenotype where InbredSetId=1 and phenotypeid=Phenotype.id limit 3; +-------+---------------+-------------+----------------------------------------------------------------------------------------------------------------------------+ | id | publicationid | phenotypeid | post_publication_description | +-------+---------------+-------------+----------------------------------------------------------------------------------------------------------------------------+ | 10001 | 116 | 4 | Central nervous system, morphology: Cerebellum weight [mg] | | 10002 | 116 | 10 | Central nervous system, morphology: Cerebellum weight after adjustment for covariance with brain size [mg] | | 10003 | 116 | 15 | Central nervous system, morphology: Brain weight, male and female adult average, unadjusted for body weight, age, sex [mg] | +-------+---------------+-------------+----------------------------------------------------------------------------------------------------------------------------+ The id field is the same that is used in the GN2 web interface and the PublicationID ties the datasets together. To list trait values: SELECT Strain.Name, PublishData.id, PublishData.value from (Strain,PublishData, PublishXRef) Where PublishData.StrainId = Strain.id limit 3; +------+---------+-------+ | Name | id | value | +------+---------+-------+ | CXB1 | 8966353 | 29.6 | | CXB1 | 8966353 | 29.6 | | CXB1 | 8966353 | 29.6 | +------+---------+-------+ here id should match dataid again: SELECT Strain.Name, PublishData.id, PublishData.value from (Strain,PublishData, PublishXRef) Where PublishData.StrainId = Strain.id and PublishXRef.dataid=8967043 and PublishXRef.dataid=PublishData.id limit 3; +------+---------+-------+ | Name | id | value | +------+---------+-------+ | BXD1 | 8967043 | 61.4 | | BXD2 | 8967043 | 49 | | BXD5 | 8967043 | 62.5 | +------+---------+-------+ *** Datasets The REST API aims to present a unified interface for genotype and phenotype data. Phenotype datasets appear in two major forms in the database and we want to present them as one resource. Dataset names are defined in ProbeSetFreeze.name and Published.id -> publication (we'll ignore the probe dataset that uses ProbeFreeze.name). These tables should be meshed. It looks like the ids are non-overlapping with the publish record IDs starting at 10,001 (someone has been smart, though it sets the limit of probesets now to 10,000). The datasets are organized differently in these tables. All published BXD data is grouped on BXDpublished with the publications as 'datasets'. So, that is how we list them in the REST API. To fetch all the datasets we first list ProbeSetFreeze entries. Then we list the Published entries. ** Fetch genotype information *** SNPs The SNP count info for the BXD is calculated like this #+begin_src python with database_connection() as conn, conn.cursor() as cursor: while startMb < endMb: # snp count cursor.execute( "SELECT COUNT(*) FROM BXDSnpPosition " "WHERE CHr = %s AND Mb >= %s AND Mb < %s " "AND StrainId1 = %s AND StrainId2 = %s", (chrName, f"{startMb:2.6f}", f"{startMb+stepMb:2.6f}", strainId1, strainId2,) ) SNPCounts.append(cursor.fetchone()[0]) startMB += stepMb #+end_src : select * from BXDSnpPosition limit 5; +------+-----------+-----------+----------+ | Chr | StrainId1 | StrainId2 | Mb | +------+-----------+-----------+----------+ | 1 | 2 | 3 | 0.002477 | | 1 | 2 | 3 | 0.002592 | | 1 | 2 | 3 | 0.00283 | | 1 | 2 | 3 | 0.002994 | | 1 | 2 | 3 | 0.003299 | +------+-----------+-----------+----------+ Other SNP tables containing select * from SnpSource limit 5; Empty set (0.00 sec) select * from SnpAll limit 5; Empty set (0.00 sec) mysql> select * from SnpAll limit 5; Empty set (0.00 sec) mysql> select * from SnpPattern limit 5; Empty set (0.00 sec) mysql> select * from SnpSource limit 5; Empty set (0.00 sec) Hmmm. This is the test database. Then there are the plink files and VCF files. ** How to fetch case attribute data To fetch case attribute data, you need the PublixshXRef Id and it's PhenotypeId. Run the following query (as an example): #+begin_src sql SELECT concat(st.Name, ',', ifnull(pd.value, 'x'), ',', ifnull(ps.error, 'x'), ',', ifnull(ns.count, 'x')) as 'Data', ifnull(ca.Name, 'x') as 'CaseAttr', ifnull(cxref.value, 'x') as 'Value' FROM PublishFreeze pf JOIN PublishXRef px ON px.InbredSetId = pf.InbredSetId JOIN PublishData pd ON pd.Id = px.DataId JOIN Strain st ON pd.StrainId = st.Id LEFT JOIN PublishSE ps ON ps.DataId = pd.Id AND ps.StrainId = pd.StrainId LEFT JOIN NStrain ns ON ns.DataId = pd.Id AND ns.StrainId = pd.StrainId LEFT JOIN CaseAttributeXRefNew cxref ON (cxref.InbredSetId = px.InbredSetId AND cxref.StrainId = st.Id) LEFT JOIN CaseAttribute ca ON ca.Id = cxref.CaseAttributeId WHERE px.Id = 10006 AND px.PhenotypeId = 28409 LIMIT 10; #+end_src * Optimize SQL? We were facing some issues with slow queries. A query was really slow on Penguin2: : time mysql -u webqtlout -pwebqtlout db_webqtl < ~/chunk.sql > /dev/null : real 0m13.082s : user 0m0.292s : sys 0m0.032s Runs in 1s on Tux01 and 13s on P2, why is that? The gist of it was increasing an InnoDB cache size(!) Interestingly, Penguin2 is running InnoDB on a much slower storage. It has more indices that Tux01(?!). Probably due to things we have been trying to make the datatables faster. Meanwhile the query is one with many joins: #+begin_src sql SELECT ProbeSet.Name,ProbeSetXRef.DataId, T4.value, T5.value, T6.value, T7.value, T8.value, T9.value, T10.value, T11.value, T12.value, T14.value, T15.value, T17.value, T18.value, T19.value, T20.value, T21.value, T22.value, T24.value, T25.value, T26.value, T28.value, T29.value, T30.value, T31.value, T35.value, T36.value, T37.value, T39.value, T98.value, T99.value, T100.value, T103.value, T487.value, T105.value, T106.value, T110.value FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) left join ProbeSetData as T4 on T4.Id = ProbeSetXRef.DataId and T4.StrainId=4 (...) left join ProbeSetData as T110 on T110.Id = ProbeSetXRef.DataId and T110.StrainId=110 WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = 'HC_M2_0606_P' and ProbeSet.Id = ProbeSetXRef.ProbeSetId order by ProbeSet.Id #+end_src And is blazingly fast on Tux01 and (now) fast enough on Penguin2. First I checked the tables for indices and storage type. Next I checked the difference in configuration. ** Check tables Tables (ProbeSetData, ProbeSet, ProbeSetXRef, ProbeSetFreeze) *** ProbeSetData Same on Tux01 and P2: : show indexes from ProbeSetData ; | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------| | ProbeSetData | 0 | DataId | 1 | Id | A | 47769944 | NULL | NULL | | BTREE | | | | ProbeSetData | 0 | DataId | 2 | StrainId | A | 5111384047 | NULL | NULL | | BTREE | | | *** ProbeSetFreeze Tux01 has less indexes than P2(!): | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------| | ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 911 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 911 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 0 | Name | 1 | Name | A | 911 | NULL | NULL | YES | BTREE | | | | ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 911 | NULL | NULL | | BTREE | | | : 4 rows in set (0.000 sec) | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------| | ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 883 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 883 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 0 | Name | 1 | Name | A | 883 | NULL | NULL | YES | BTREE | | | | ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 883 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 1 | ShortName | 1 | ShortName | A | 883 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 1 | ProbeFreezeId | 1 | ProbeFreezeId | A | 441 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 1 | conf_and_public | 1 | confidentiality | A | 3 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 1 | conf_and_public | 2 | public | A | 4 | NULL | NULL | | BTREE | | | : 8 rows in set (0.00 sec) *** ProbeSet Identical indexes *** ProbeSetXRef Tux01 has less indexes than P2(!): : MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------| | ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 885 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 0 | ProbeSetId | 2 | ProbeSetId | A | 47713039 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 47713039 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 1 | Locus_IDX | 1 | Locus | A | 15904346 | NULL | NULL | YES | BTREE | | | : 4 rows in set (0.000 sec) : MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------| | ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 856 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 0 | ProbeSetId | 2 | ProbeSetId | A | 46412145 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 46412145 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 1 | ProbeSetId1 | 1 | ProbeSetId | A | 5156905 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 1 | Locus | 1 | Locus | A | 23206072 | NULL | NULL | YES | BTREE | | | : 5 rows in set (0.00 sec) ** Check storage The database in Tux01 is mounted on NVME. On Penguin2 it is slower SATA with RAID5. Also on Penguin2 the following tables are using InnoDB instead of MyISAM #+begin_src sh -rw-rw---- 1 mysql mysql 79691776 Oct 15 2019 AccessLog.ibd -rw-rw---- 1 mysql mysql 196608 Oct 24 2019 Docs.ibd -rw-rw---- 1 mysql mysql 63673729024 Jul 10 2020 GenoData.ibd -rw-rw---- 1 mysql mysql 34787557376 Jul 9 2020 ProbeData.ibd -rw-rw---- 1 mysql mysql 254690721792 Jul 10 2020 ProbeSetData.ibd -rw-rw---- 1 mysql mysql 32103202816 Jul 9 2020 SnpAll.ibd -rw-rw---- 1 mysql mysql 98304 May 6 2020 TraitMetadata.ibd #+end_src This [[https://www.liquidweb.com/kb/mysql-performance-myisam-vs-innodb/][article]] suggests that myISAM will be faster for our use case. ** Configuration There was one setting on Tux01 missing on P2 : +innodb_buffer_pool_size=1024M Running the same query twice (so you can see the warmup after a restart of MariaDB) #+begin_src sh penguin2:/etc$ time mysql -u webqtlout -pwebqtlout db_webqtl < ~/chunk.sql > ~/test.out real 0m4.253s user 0m0.276s sys 0m0.040s penguin2:/etc$ time mysql -u webqtlout -pwebqtlout db_webqtl < ~/chunk.sql > ~/test.out real 0m2.633s user 0m0.296s sys 0m0.028s #+end_src That is much better :)