Re: Time Manipulation In Query

[Rick James]

> 1)Yes for now I am using smaller int later I will have to move to larger int.
Declare the size of INT you will eventually need. Do it NOW, not later, because ALTER TABLE is costly on huge tables.

> So those running terabytes of data what do they use as their id? Do you think I should make now itself as big int?
Do the math!
* MEDIUMINT UNSIGNED: 16M -- if you might have more than that many rows, then MEDIUMINT UNSIGNED is too small.
* INT UNSIGNED: 4 Billion -- if you might have more than that many rows, then INT UNSIGNED is too small; use BIGINT.
* Nothing (in your lifetime) will count past the range of BIGINT.

> 3) Which is the data and index size and how do you compare it against buffer_pool_size.
* If you are using InnoDB: If the sum of all Data_lengths, plus all Index_lengths, is less than innodb_buffer_pool_size, then all the data and indexes can be cached in RAM.
* If you are expecting terabytes, then you won't be able to cache everything in RAM (on today's hardware).

> I dont see it part of the show table status?
Let's see your SHOW TABLE STATUS.

> Data_free any rule of thumb for it? What is this data_free represents?
If you never delete from the table, ignore the value. If you have a lot of churn in the table, then this could be a clue of wasted space. Generally, Data_free is not a useful number.

> Data_length is total memory space used right?
No. It has nothing to do with "memory", which normally refers to RAM. It refers to the _data_ part of the disk space used. (Index_length is the other part.)

> Avg_row_length- most of the fields I read together escpecially the geoFenceName, but the description I rarely use it.
Eh?

> The field geoFenceString I set as text is that ok or that is a main cause of the avg_row_length to be big?
I can't tell without looking at your data! Do this to answer your question:
SELECT AVG(LENGTH(geoFenceString)) FROM tbl;
That field is not in any of the tables you have shown!!