From d6d339adbbdbf18e5acabaa180092184cabacec8 Mon Sep 17 00:00:00 2001 From: Pjotr Prins Date: Tue, 13 Apr 2021 02:43:03 -0500 Subject: SQL notes - table layout --- doc/database.org | 11 +---------- 1 file changed, 1 insertion(+), 10 deletions(-) (limited to 'doc/database.org') diff --git a/doc/database.org b/doc/database.org index cd833b83..dd068d71 100644 --- a/doc/database.org +++ b/doc/database.org @@ -1417,28 +1417,23 @@ 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 | | | @@ -1449,7 +1444,6 @@ Tux01 has less indexes than P2(!): | 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 @@ -1461,19 +1455,17 @@ Identical indexes 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 | | | @@ -1481,7 +1473,6 @@ Tux01 has less indexes than P2(!): | 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 -- cgit v1.2.3