Mysql> Select COUNT (*) from userinfo;
+----------+
| COUNT (*) |
+----------+
| 115597 |
+----------+
1 row in Set (0.00 sec)
Mysql> Select concat (Truncate (SUM (data_length)/1024/1024,3), ' MB ') as Data_size,
-Concat (Truncate (SUM (max_data_length)/1024/1024,3), ' MB ') as Max_data_leng
Th
-Concat (Truncate (SUM (data_free)/1024/1024,3), ' MB ') as Data_free,
-Concat (Truncate (SUM (index_length)/1024/1024,3), ' MB ') as Index_length
From Information_schema.tables where table_name= ' userinfo ';
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 21.477MB | 268435455.999MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
The following test tinyint
mysql> ALTER TABLE ' userinfo '
ADD COLUMN ' type ' tinyint not NULL DEFAULT 0 COMMENT ' reaction type ' after ' I
Ntegral ';
Query OK, 115597 rows affected (0.54 sec)
records:115597 duplicates:0 warnings:0
Mysql> Select concat (Truncate (SUM (data_length)/1024/1024,3), ' MB ') as Data_size,
-Concat (Truncate (SUM (max_data_length)/1024/1024,3), ' MB ') as Max_data_leng
Th
-Concat (Truncate (SUM (data_free)/1024/1024,3), ' MB ') as Data_free,
-Concat (Truncate (SUM (index_length)/1024/1024,3), ' MB ') as Index_length
From Information_schema.tables where table_name= ' userinfo ';
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 21.477MB | 268435455.999MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
mysql> INSERT INTO UserInfo (App,imei,type) VALUES (', ' 0 ', 43);
Query OK, 1 row Affected (0.00 sec)
Mysql> Select concat (Round (sum (data_length/1024/1024), 3), ' MB ') as Data_size,
-Concat (Round (sum (max_data_length/1024/1024), 3), ' MB ') as Max_data_length,
-Concat (Round (sum (data_free/1024/1024), 3), ' MB ') as Data_free,
-Concat (Round (sum (index_length/1024/1024), 3), ' MB ') as Index_length
-From Information_schema.tables where table_name= ' userinfo '
;
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 21.478MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in Set (0.00 sec)
mysql> update userinfo set type=30;
Query OK, 115598 rows Affected (2.70 sec)
Rows matched:115598 changed:115598 warnings:0
Mysql> Select concat (Round (sum (data_length/1024/1024), 3), ' MB ') as Data_size,
-Concat (Round (sum (max_data_length/1024/1024), 3), ' MB ') as Max_data_length,
-Concat (Round (sum (data_free/1024/1024), 3), ' MB ') as Data_free,
-Concat (Round (sum (index_length/1024/1024), 3), ' MB ') as Index_length
From Information_schema.tables where table_name= ' userinfo ';
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 22.038MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in Set (0.00 sec)
The following test enum
Mysql> Select concat (Round (sum (data_length/1024/1024), 3), ' MB ') as Data_size,
-Concat (Round (sum (max_data_length/1024/1024), 3), ' MB ') as Max_data_length,
-Concat (Round (sum (data_free/1024/1024), 3), ' MB ') as Data_free,
-Concat (Round (sum (index_length/1024/1024), 3), ' MB ') as Index_length
From Information_schema.tables where table_name= ' userinfo ';
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 21.478MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
mysql> ALTER TABLE ' userinfo '
ADD COLUMN ' type ' enum (' unknown ', ' compound ', ' decomposition ', ' permutation ', ' complex decomposition ', ' substitution ', ' plus ')
, ' eliminate ', ' Add poly ', ' esterification ', ' hydrolysis ', ' polymerization ', ' polycondensation ', ' endothermic ', ' exothermic ', ' oxidation ', ' restore ') after ' I
Ntegral ';
Query OK, 115597 rows affected (0.63 sec)
records:115597 duplicates:0 warnings:0
Mysql> Select concat (Round (sum (data_length/1024/1024), 3), ' MB ') as Data_size,
-Concat (Round (sum (max_data_length/1024/1024), 3), ' MB ') as Max_data_length,
-Concat (Round (sum (data_free/1024/1024), 3), ' MB ') as Data_free,
-Concat (Round (sum (index_length/1024/1024), 3), ' MB ') as Index_length
From Information_schema.tables where table_name= ' userinfo ';
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 21.694MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in Set (0.00 sec)
mysql> Update userinfo set type= ' complex decomposition ';
Query OK, 115597 rows affected (2.54 sec)
Rows matched:115597 changed:115597 warnings:0
Mysql> Select concat (Round (sum (data_length/1024/1024), 3), ' MB ') as Data_size,
-Concat (Round (sum (max_data_length/1024/1024), 3), ' MB ') as Max_data_length,
-Concat (Round (sum (data_free/1024/1024), 3), ' MB ') as Data_free,
-Concat (Round (sum (index_length/1024/1024), 3), ' MB ') as Index_length
From Information_schema.tables where table_name= ' userinfo ';
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 21.694MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in Set (0.00 sec)
The following test set
Mysql> Select concat (Round (sum (data_length/1024/1024), 3), ' MB ') as Data_size,
, concat (Round (SUM (max_ data_length/1024/1024), 3, ' MB ') as Max_data_length,
--concat (Round (sum (data_free/1024/1024), 3), ' MB ') as data _free,
-concat (Round (sum (index_length/1024/1024), 3), ' MB ') as Index_length
, from Information_ Schema.tables where table_name= ' userinfo '
;
+-----------+-----------------+-----------+--------------+
| data_size | max_data_length | data_free | index_ Length |
+-----------+-----------------+-----------+--------------+
| 21.478MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in Set (0.00 sec)
mysql> ALTER TABLE ' userinfo '
, ADD COLUMN ' type ' Set (' Unknown ', ' compound ', ' decomposition ', ' permutation ', ' complex decomposition ', ' replace ', ' Add ',
' eliminate ', ' Add poly ', ' esterification ', ' hydrolysis ', ' polymerization ', ' polycondensation ', ' endothermic ', ' exothermic ', ' oxidation ', ' restore ', after ' in
Tegral ';
Query OK, 115597 rows affected (0.61 sec)
records:115597 duplicates:0 warnings:0
Mysql> Select concat (Round (sum (data_length/1024/1024), 3), ' MB ') as Data_size,
-Concat (Round (sum (max_data_length/1024/1024), 3), ' MB ') as Max_data_length,
-Concat (Round (sum (data_free/1024/1024), 3), ' MB ') as Data_free,
-Concat (Round (sum (index_length/1024/1024), 3), ' MB ') as Index_length
From Information_schema.tables where table_name= ' userinfo ';
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 21.590MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in Set (0.01 sec)
mysql> Update userinfo set type= ' Plus ';
Query OK, 115597 rows Affected (3.63 sec)
Rows matched:115597 changed:115597 warnings:0
Mysql> Select concat (Round (sum (data_length/1024/1024), 3), ' MB ') as Data_size,
-Concat (Round (sum (max_data_length/1024/1024), 3), ' MB ') as Max_data_length,
-Concat (Round (sum (data_free/1024/1024), 3), ' MB ') as Data_free,
-Concat (Round (sum (index_length/1024/1024), 3), ' MB ') as Index_length
From Information_schema.tables where table_name= ' userinfo ';
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 23.235MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in Set (0.00 sec)
mysql> Update userinfo Set type= ' Add, replace, eliminate, radiate ';
Query OK, 115597 rows Affected (4.37 sec)
Rows matched:115597 changed:115597 warnings:0
Mysql> Select concat (Round (sum (data_length/1024/1024), 3), ' MB ') as Data_size,
-Concat (Round (sum (max_data_length/1024/1024), 3), ' MB ') as Max_data_length,
-Concat (Round (sum (data_free/1024/1024), 3), ' MB ') as Data_free,
-Concat (Round (sum (index_length/1024/1024), 3), ' MB ') as Index_length
From Information_schema.tables where table_name= ' userinfo ';
+-----------+-----------------+-----------+--------------+
| Data_size | Max_data_length | Data_free | Index_length |
+-----------+-----------------+-----------+--------------+
| 23.235MB | 268435456.000MB | 0.000MB | 1.319MB |
+-----------+-----------------+-----------+--------------+
1 row in Set (0.01 sec)
Exploration of spatial efficiency of database type (iv)-tinyint and enum and set