MySQL Forums
Forum List  »  InnoDB

Curious size variations
Posted by: JEAN LUSETTI
Date: January 20, 2023 10:02AM

Hi,

I am facing curious storage differences in size for 2 similar tables:
CREATE TABLE TSENSOR (
DateTime DATETIME NOT NULL,
SensorID TINYINT UNSIGNED,
Value FLOAT,
PRIMARY KEY(DateTime,SensorID));

CREATE TABLE SENSORS (
DateTime DATETIME NOT NULL,
SensorID SMALLINT UNSIGNED,
Value FLOAT,
PRIMARY KEY(DateTime,SensorID));

Only difference is SensorID as TINYINT in TSENSOR and as SMALLINT in SENSORS, should be only 1 byte more per record for SENSORS.
Indexes have been created identically:
CREATE INDEX SensorID ON TSENSOR(SensorID);
CREATE INDEX DateTime ON TSENSOR(DateTime);

CREATE INDEX SensorID ON SENSORS(SensorID);
CREATE INDEX DateTime ON SENSORS(DateTime);

However, for similar number of elements, table size is very different (more than 4x):
SELECT COUNT(*) FROM TSENSOR;
+----------+
| COUNT(*) |
+----------+
| 3308886 |
+----------+

SELECT COUNT(*) FROM SENSORS;
+----------+
| COUNT(*) |
+----------+
| 3581182 |
+----------+


source /home/pi/mysql/dbsize.sql;
+---------+-----------------+----------------+-----------------+
| Table | Total Size (MB) | Data Size (MB) | Index Size (MB) |
+---------+-----------------+----------------+-----------------+
| SENSORS | 511 | 390 | 121 |
| TSENSOR | 111 | 61 | 50 |

dbsize.sql is as follows:
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Total Size (MB)`,
ROUND((DATA_LENGTH) / 1024 / 1024) AS `Data Size (MB)`,
ROUND((INDEX_LENGTH) / 1024 / 1024) AS `Index Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "t_monitoring"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;

So basically, each record (based on Total Size) in TSENSOR is about 34 bytes whereas it does about 154 bytes in SENSORS.

I populated data from TSENSOR to SENSORS (I don't think it really matters here but to make sure) using:
INSERT INTO SENSORS (DateTime,SensorID,Value)
SELECT DateTime,66*256+SensorID,Value
FROM BSENSOR;

INSERT INTO SENSORS (DateTime,SensorID,Value)
SELECT DateTime,84*256+SensorID,Value
FROM TSENSOR;

Does someone have an explanation?
Thanks,
Jean

Options: ReplyQuote


Subject
Views
Written By
Posted
Curious size variations
384
January 20, 2023 10:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.