Re: Time Manipulation In Query

[Rick James]

If you are expecting the table to be huge, then you should be more careful with datatypes.

> int(5)
An INT takes 4 bytes, regardless of the number after it. In fact, the number is useless except in one obscure situation.
MEDIUMINT takes 3 bytes and has a range of +/-8M (0..16M if UNSIGNED)
SMALLINT takes 2 bytes and has a range of +/-32K (0..64K if UNSIGNED)
Recommend you switch to a smaller datatype.

> `geoFenceType` varchar(255) NOT NULL,
How many different FenceTypes might there be? Are their names really that long?
Consider ENUM or "normalizing" the types into another table.

> What are the crucial values to look from Show status table?
Data and index size -- to get a feel for how it compares to the buffer_pool_size.
Number of rows (approx, since it is InnoDB) -- may impact how the query runs.
Avg row length -- under 100 bytes is not an issue; if over, it may trigger me to look for bulky items in the rows and question whether the tables are designed "right".
Data_free -- if large, you may have "churn" due to frequent deletes. That leads to another discussion.
So...
> Data_length: 114688
Tiny. But you have explained that it will grow.
> Avg_row_length: 655
Big. You have 4 fields that are varchar(255). Do you usually read those fields? Or are they just informative in rare cases. If the latter, maybe they could be put into another table ("vertical partitioning"). Also, consider compressing big fields before storing them -- smaller -> more cacheable -> faster (and the CPU overhead is not bad).