diff options
| -rw-r--r-- | issues/systems/mariadb/move-to-innodb.gmi | 53 | ||||
| -rw-r--r-- | tasks/pjotrp.gmi | 20 |
2 files changed, 36 insertions, 37 deletions
diff --git a/issues/systems/mariadb/move-to-innodb.gmi b/issues/systems/mariadb/move-to-innodb.gmi index c6b52c7..924a203 100644 --- a/issues/systems/mariadb/move-to-innodb.gmi +++ b/issues/systems/mariadb/move-to-innodb.gmi @@ -664,6 +664,7 @@ CREATE TABLE 'ProbeSetFreeze' ( There is a primary key. Good. +``` ALTER TABLE ProbeSetSE ADD PRIMARY KEY(DataId,StrainId); Query OK, 688744613 rows affected (15 min 13.830 sec) @@ -920,12 +921,14 @@ Or ``` SHOW CREATE TABLE ProbeSet; +``` E.g. - FULLTEXT KEY `SEARCH_GENE_IDX` (`Symbol`,`alias`), - FULLTEXT KEY `SEARCH_FULL_IDX` (`Name`,`description`,`Symbol`,`alias`,`GenbankId`,`UniGeneId`,`Probe_Target_Description`), - FULLTEXT KEY `RefSeq_FULL_IDX` (`RefSeq_TranscriptId`) +``` +FULLTEXT KEY 'SEARCH_GENE_IDX' ('Symbol','alias'), + FULLTEXT KEY 'SEARCH_FULL_IDX' ('Name','description','Symbol','alias','GenbankId','UniGeneId','Probe_Target_Description'), + FULLTEXT KEY 'RefSeq_FULL_IDX' ('RefSeq_TranscriptId') ``` To see all: @@ -995,10 +998,10 @@ CREATE FULLTEXT INDEX ft_ProbeSet_Symbol ON ProbeSet(Symbol); Now we have ``` -FULLTEXT KEY `SEARCH_GENE_IDX` (`Symbol`,`alias`), -FULLTEXT KEY `SEARCH_FULL_IDX` (`Name`,`description`,`Symbol`,`alias`,`GenbankId`,`UniGeneId`,`Probe_Target_Description`), -FULLTEXT KEY `RefSeq_FULL_IDX` (`RefSeq_TranscriptId`), -FULLTEXT KEY `ft_ProbeSet_Symbol` (`Symbol`) +FULLTEXT KEY 'SEARCH_GENE_IDX' ('Symbol','alias'), +FULLTEXT KEY 'SEARCH_FULL_IDX' ('Name','description','Symbol','alias','GenbankId','UniGeneId','Probe_Target_Description'), +FULLTEXT KEY 'RefSeq_FULL_IDX' ('RefSeq_TranscriptId'), +FULLTEXT KEY 'ft_ProbeSet_Symbol' ('Symbol') ``` and the query works. @@ -1061,24 +1064,24 @@ select count(*) from ProbeSetFreeze limit 2; | 931 | +----------+ SHOW CREATE TABLE ProbeSetFreeze; -CREATE TABLE `ProbeSetFreeze` ( - `Id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, - `ProbeFreezeId` smallint(5) unsigned NOT NULL DEFAULT 0, - `AvgID` smallint(5) unsigned NOT NULL DEFAULT 0, - `Name` varchar(40) DEFAULT NULL, - `Name2` varchar(100) NOT NULL DEFAULT '', - `FullName` varchar(100) NOT NULL DEFAULT '', - `ShortName` varchar(100) NOT NULL DEFAULT '', - `CreateTime` date NOT NULL DEFAULT '0000-00-00', - `OrderList` int(5) DEFAULT NULL, - `public` tinyint(4) NOT NULL DEFAULT 0, - `confidentiality` tinyint(4) NOT NULL DEFAULT 0, - `AuthorisedUsers` varchar(300) NOT NULL, - `DataScale` varchar(20) NOT NULL DEFAULT 'log2', - PRIMARY KEY (`Id`), - UNIQUE KEY `FullName` (`FullName`), - UNIQUE KEY `Name` (`Name`), - KEY `NameIndex` (`Name2`) +CREATE TABLE 'ProbeSetFreeze' ( + 'Id' smallint(5) unsigned NOT NULL AUTO_INCREMENT, + 'ProbeFreezeId' smallint(5) unsigned NOT NULL DEFAULT 0, + 'AvgID' smallint(5) unsigned NOT NULL DEFAULT 0, + 'Name' varchar(40) DEFAULT NULL, + 'Name2' varchar(100) NOT NULL DEFAULT '', + 'FullName' varchar(100) NOT NULL DEFAULT '', + 'ShortName' varchar(100) NOT NULL DEFAULT '', + 'CreateTime' date NOT NULL DEFAULT '0000-00-00', + 'OrderList' int(5) DEFAULT NULL, + 'public' tinyint(4) NOT NULL DEFAULT 0, + 'confidentiality' tinyint(4) NOT NULL DEFAULT 0, + 'AuthorisedUsers' varchar(300) NOT NULL, + 'DataScale' varchar(20) NOT NULL DEFAULT 'log2', + PRIMARY KEY ('Id'), + UNIQUE KEY 'FullName' ('FullName'), + UNIQUE KEY 'Name' ('Name'), + KEY 'NameIndex' ('Name2') ) ENGINE=MyISAM AUTO_INCREMENT=1054 DEFAULT CHARSET=latin1 ``` diff --git a/tasks/pjotrp.gmi b/tasks/pjotrp.gmi index a327f5d..278323b 100644 --- a/tasks/pjotrp.gmi +++ b/tasks/pjotrp.gmi @@ -16,7 +16,7 @@ The tasks here should probably be broken out into appropriately tagged issues, w ## Mempang24 -* [+] Invites +* [X] Invites * [+] Payments * [+] Rooms * [+] Catering @@ -28,7 +28,6 @@ The tasks here should probably be broken out into appropriately tagged issues, w * [+] Julia as part of GN3 deployment => /topics/deploy/julia.gmi * [+] Shelby's paper+application -* [+] Debian/free software issues incl. vcflib work in Zig and release # Tasks @@ -36,8 +35,13 @@ The tasks here should probably be broken out into appropriately tagged issues, w Now (X=done +=WIP _=kickoff ?=?) +* [ ] ~Test GEMMA/bulklmm speedups & paper +* [ ] GEMMA batch run and precompute +=> ../issues/gemma/lmm-precomputed-scores +* [ ] Genotyping and sparse VCF +* [ ] GeneNetwork consortium (paper & CTC) + * [+] Build leadership team -* [+] Build GN consortium * [+] gBAM * [ ] Audit DB security * [ ] p-value global search @@ -45,12 +49,6 @@ Now (X=done +=WIP _=kickoff ?=?) => ../issues/systems/octopus -* [ ] GEMMA batch run and precompute - -=> ../issues/gemma/lmm-precomputed-scores - -* [ ] GEMMA/bulklmm speedups -* [ ] bulklmm paper * [ ] Add R/qtl2 and multi-parent support with Karl (DO and Magic populations) * [ ] Fix slow search on Mariadb? * [.] GeneNetwork paper @@ -85,9 +83,6 @@ Later => ./machine-room.gmi machine room -* [+] move backup dirs on tux01 to a larger partition (from export3 to export2) -* [.] Check Tony's list and improve search for SNPs and Hs -* [ ] GeneNetwork consortium (paper & CTC) * [ ] Fix issues: => https://genenetwork.org/show_trait?trait_id=10441&dataset=HSNIH-PalmerPublish slow mapping => http://gn1.genenetwork.org/webqtl/main.py?FormID=sharinginfo&GN_AccessionId=2 @@ -108,3 +103,4 @@ Later * [X] Set up cgit as a service * [X] Fix mariadb index search - need to upgrade mariadb to convert final utf8mb4, see => ../issues/slow-sql-query-for-xapian-indexing.gmi +* [X] Debian/free software issues incl. vcflib work in Zig and release |