From 995507d63185c93e20e7c5ff905914535eb11e33 Mon Sep 17 00:00:00 2001
From: Pjotr Prins
Date: Thu, 30 Dec 2021 06:52:28 +0100
Subject: Innodb and ProbeSetXREf

---
 topics/systems/mariadb/ProbeSetXRef.gmi   | 58 ++++++++++++++++++++++++++++++-
 topics/systems/mariadb/move-to-innodb.gmi | 12 +++++++
 2 files changed, 69 insertions(+), 1 deletion(-)

(limited to 'topics/systems/mariadb')

diff --git a/topics/systems/mariadb/ProbeSetXRef.gmi b/topics/systems/mariadb/ProbeSetXRef.gmi
index d9adad1..db0be0a 100644
--- a/topics/systems/mariadb/ProbeSetXRef.gmi
+++ b/topics/systems/mariadb/ProbeSetXRef.gmi
@@ -60,18 +60,74 @@ MariaDB [db_webqtl]> select count(*) from ProbeSetXRef;
 
 ```
 rg ProbeSetXRef --color=always |less -R
+rg ProbeSetXRef --type=py -l|fzf --preview="rg --color=always -A 20 ProbeSetXRef {}" --preview-window=right:85%:wrap
 ```
 
+In SQL where statements these are usually combined
+
+ProbeSetXRef.ProbeSetFreezeId
+ProbeSetXRef.ProbeSetId
+
+and sometimes
+
+ProbeSetXRef.DataId
+ProbeSetXRef.Locus
+
+As can be seen from above table definition the indices are matching
+
+```
+  UNIQUE KEY 'ProbeSetId' ('ProbeSetFreezeId','ProbeSetId'),
+  UNIQUE KEY 'DataId_IDX' ('DataId'),
+  KEY 'Locus_IDX' ('Locus')
+```
+
+The combination of
+
+```
+select count(distinct ProbeSetFreezeId,ProbeSetId) from ProbeSetXRef limit 10;
+```
+
+is unique.
+
+
 ### Create test
 
 Some select statement and maybe a page of GN2.
 
+```
+MariaDB [db_webqtl]> select count(*) from ProbeSetXRef where ProbeSetFreezeId<200 and ProbeSetId<1000 and pValue>0.5;
++----------+
+| count(*) |
++----------+
+|    19068 |
++----------+
+1 row in set (1.752 sec)
+
+MariaDB [db_webqtl]> select count(*) from ProbeSetXRef where ProbeSetFreezeId<300 and ProbeSetId<1000 and pValue>0.5;
++----------+
+| count(*) |
++----------+
+|    19068 |
++----------+
+1 row in set (13.781 sec)
+```
+
+Note the second query traverses the full file.
+
 ### Create primary key
 
 ```
 ALTER TABLE ProbeSetXRef
-  ADD PRIMARY KEY(col1,col2);
+  ADD PRIMARY KEY(ProbeSetFreezeId,ProbeSetId);
+```
+
+@@
+
 ```
+MariaDB [db_webqtl]> DROP INDEX ProbeSetId ON ProbeSetXRef;
+MariaDB [db_webqtl]> show index from ProbeSetXRef;
+```
+
 
 ### Create indices
 ### Convert to innodb
diff --git a/topics/systems/mariadb/move-to-innodb.gmi b/topics/systems/mariadb/move-to-innodb.gmi
index f93aace..f5011cb 100644
--- a/topics/systems/mariadb/move-to-innodb.gmi
+++ b/topics/systems/mariadb/move-to-innodb.gmi
@@ -348,6 +348,11 @@ Prototocol from
 
 Every table update has to follow the template:
 
+### Check recent backups
+
+* [ ] ascertain there is a backup
+* [ ] copy original files
+
 ### Check table structure
 
 ```
@@ -366,6 +371,7 @@ select count(*) from MYTABLE;
 
 ```
 rg MYTABLE --color=always |less -R
+rg MYTABLE --type=py -l|fzf --preview="rg --color=always -A 20 MYTABLE {}" --preview-window=right:85%:wrap
 ```
 
 ### Create test
@@ -380,6 +386,12 @@ ALTER TABLE MYTABLE
 ```
 
 ### Create indices
+
+```
+SHOW INDEX FROM MYTABLE;
+DROP INDEX ProbeSetId ON MYTABLE;
+```
+
 ### Convert to innodb
 
 ```
-- 
cgit v1.2.3