distal-attribute
distal-attribute
distal-attribute
distal-attribute

Total number of bytes used per record?

User, date Message
Written by BenH
2 years ago
Category: General
5 posts since Tue, 10 Jul 12
When creating a new table and assigning the various data types and lengths, is there a way (for optimisation purposes) to tell the total space (in bytes) that are required for each record. I hope that makes sense.... I'm new to this!
Written by ansgar
2 years ago
4973 posts since Fri, 07 Apr 06
You can query your rows in such a way:

SELECT
LENGTH(col1)+LENGTH(col2)+LENGTH(col3)+...
FROM table


Should deliver reliable sizes at least for text based data types such as VARCHAR and TEXT.
Written by BenH
2 years ago
5 posts since Tue, 10 Jul 12
Many thanks for your reply although it wasn't quite what I was looking for. Could I suggest this as a feature to be added to a future version? I think it would be pretty useful.
Written by ansgar
2 years ago
4973 posts since Fri, 07 Apr 06
A feature? What should that feature look like? We talked about some calculation here, not about some GUI feature in HeidiSQL, did we?
Written by BenH
2 years ago
5 posts since Tue, 10 Jul 12
My apologies for not being clear. I was thinking of it as a feature of the GUI.
Written by ansgar
2 years ago
4973 posts since Fri, 07 Apr 06
Well, describe it then, please.
Written by BenH
2 years ago
5 posts since Tue, 10 Jul 12
OK, this is how it came about...
I was creating a new table with about 20 columns of all different data types which I was trying to optimise with regard to the storage size for each record. It's the first time I have used a database so am not totally clear on the exactly storage size of each data type. So I thought it would be useful (to me at least) to have an indication of the total storage required for the records in the table (as you add new columns). It would help make sure the records weren't taking up more space than they need to.
Is it clear now what I mean?
Written by kalvaro
2 years ago
592 posts since Thu, 29 Nov 07
This must be the first time I hear database design in such terms... What are you trying to accomplish? Save a string like "Hello" serialized as integer if that saves a byte?

If you could provide a real life example of those optimizations you have in mind, we could probably help you point out the precise misconception.
Written by jfalchMoney, Euro
2 years ago
385 posts since Sat, 17 Oct 09
for what it´s worth, 11.5. Data Type Storage Requirements contains details.

however, i must side with kalvaro; size-optimizing db record structures by hand in the age of terabyte drives seems to me a strangely retro kind of hobby.

one alternative adhoc strategy that i have used myself would be:
1) make fields quite large;
2) wait until table contains a sizeable amount of data;
3) statistically analyze field usage, and optionally shrink some fields accordingly.
Written by BenH
2 years ago
5 posts since Tue, 10 Jul 12
Ok, it's beginning to make sense why you guys think my question is so strange. I come from a world (programming microcontrollers) where memory is limited optimisation is important. My mind set it to 'optimise where one can' and 'why use more than you need to'. The records (which contain a mixture of datetime, char, charvar, int and floats) are to hold data from a remote monitoring system I have developed and I hope will grow to be widely used. Each individual system can have 12 sensors which could each be recording 24 records per day so it could (hopefully) end up being a very large amount of data. My thinking is to get it right now rather than have to do it again when I find out my database it taking up a lot of hard drive space on a web server.
Written by kalvaro
2 years ago
592 posts since Thu, 29 Nov 07
Large time based datasets are good candidates for partitioning:

http://dev.mysql.com/doc/refman//5.5/en/partitioning.html

 

Please login to leave a reply, or register at first.