Skip navigation links

MySQL Forums :: General :: Time Manipulation In Query


Advanced Search

Re: Time Manipulation In Query
Posted by: Rick James ()
Date: November 25, 2011 12:24PM

> 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!!

Options: ReplyQuote


Subject Written By Posted
Time Manipulation In Query newbie Shai 10/23/2011 03:13PM
Re: Time Manipulation In Query Rick James 10/24/2011 10:26AM
Re: Time Manipulation In Query newbie Shai 10/25/2011 09:22AM
Re: Time Manipulation In Query Rick James 10/25/2011 05:26PM
Re: Time Manipulation In Query newbie Shai 10/25/2011 11:26PM
Re: Time Manipulation In Query Rick James 10/26/2011 09:31PM
Re: Time Manipulation In Query newbie Shai 10/26/2011 08:24PM
Re: Time Manipulation In Query newbie Shai 10/28/2011 11:27PM
Re: Time Manipulation In Query Rick James 10/30/2011 12:28PM
Re: Time Manipulation In Query newbie Shai 11/01/2011 07:35PM
Re: Time Manipulation In Query Rick James 11/21/2011 11:50PM
Re: Time Manipulation In Query newbie Shai 11/23/2011 11:27AM
Re: Time Manipulation In Query Rick James 11/24/2011 11:39AM
Re: Time Manipulation In Query newbie Shai 11/24/2011 12:11PM
Re: Time Manipulation In Query Rick James 11/25/2011 12:24PM
Re: Time Manipulation In Query newbie Shai 11/26/2011 01:54AM
Re: Time Manipulation In Query Rick James 11/26/2011 10:44PM
Re: Time Manipulation In Query newbie Shai 11/27/2011 04:15AM
Re: Time Manipulation In Query Rick James 11/28/2011 11:09AM
Re: Time Manipulation In Query newbie Shai 11/28/2011 07:52PM
Re: Time Manipulation In Query Rick James 11/29/2011 09:02PM
Re: Time Manipulation In Query newbie Shai 11/30/2011 07:29AM
Re: Time Manipulation In Query newbie Shai 11/20/2011 11:46PM


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.