If you don't specify a length for a data type/column, does that affect performance?

[greenace92]

I'm thinking of using two separate columns for Date and Time, so then I'm hoping I can use the built in search functions of MySQL to sift through dates for example a range.

 

I see that the DATE column has a length of 10 with YYYY-mm-dd and time has 8 with HH:ii:ss

 

If I don't specify lengths does that matter?

 

When I searched this in Google: if you don't specify length of column does that affect performance mysql

 

The first three results were from stack overflow with "...importance of varchar length..." is it only varchar?

There was something about using Text too which was slow. I use that for things that have indeterminite length and are expected to be large like bodies of comments or posts/messages.

 

Thansk for any help.

[requinix]

Have you tried looking at the documentation?

 

Dates and times don't even have lengths. The string values do, sure, but the data type itself doesn't.

Lengths only matter on field types that (can) have variable length data. Like strings and numbers: for VARCHARs it is the maximum character length stored, while for *INTs it is only the display width.

[Barand]

Check out the MySql manual for storage requirements

http://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

 

 

Storage Requirements for Date and Time Types

For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from tables created from 5.6.4 on. This is due to a change in 5.6.4 that permits these types to have a fractional part, which requires from 0 to 3 bytes.

Data Type    Storage Required Before MySQL 5.6.4    Storage Required as of MySQL 5.6.4

YEAR         1 byte                                 1 byte
DATE         3 bytes                                3 bytes
TIME         3 bytes                                3 bytes + fractional seconds storage
DATETIME     8 bytes                                5 bytes + fractional seconds storage
TIMESTAMP    4 bytes                                4 bytes + fractional seconds storage

[greenace92]

Have you tried looking at the documentation?

 

Dates and times don't even have lengths. The string values do, sure, but the data type itself doesn't.

Lengths only matter on field types that (can) have variable length data. Like strings and numbers: for VARCHARs it is the maximum character length stored, while for *INTs it is only the display width.

 

That's a great response. I have looked at the documentation in the past. In this particular instance, I did not look. I looked at some stack overflow posts first then came here.

 

Barand

 

Thanks for the link and the quote. That's great to see actual numbers (bytes) I can begin to compute things, expected load, number of connetions etc.

 

Yeah I apologize, I'll mark this question as solved with requinix's response.

Thanks to Barand as well.

 

I need to go do some reading.

[benanamen]

I have read several of your posts. For some reason you are trying to go way, way beyond anywhere you are currently at in knowledge or need.

 

It's like worrying about how to do brain surgery when you don't even know how to put a band aid on a scratch all the while not even in Med school. Get down on the basics. It is clear you have not. This is a very rare instance where I think you are just wasting peoples time.

Edited May 6, 2016 by benanamen

[greenace92]

I have read several of your posts. For some reason you are trying to go way, way beyond anywhere you are currently at in knowledge or need.

 

It's like worrying about how to do brain surgery when you don't even know how to put a band aid on a scratch all the while not even in Med school. Get down on the basics. It is clear you have not. This is a very rare instance where I think you are just wasting peoples time.

 

This is not a response in spite, which "basics" in particular are you referring to?

 

I'm trying to get positioining down, understanding block/flow of websites, I've been looking at the W3C visual formatting model.

 

I guess I haven't had the time to read the entire manual of PHP/MySQL but I get the point of "researching before you ask".

And as far as worrying about things beyond my scope at this time, why not? I know that there's a lot of things I don't know, and don't need yet. But I'd like to get an idea ahead of time, and start learning about it.

 

What is this bit about "This is a very rare instance where I think you are just wasting peoples time." ?

 

I always wonder/ask what is the incentive why people hang around forums and answer questions. Why don't they charge money? A friend answered me and said "They were in that position(not knowing) so they try to help others." I would like to do the same but right now I'm at the instance or position in my web development journey of scrapping together material and trying to build things.

I accept and acknowledge that I am in the wrong about asking help before really having tried to search/find out for myself. I realize it's easy to say things too without owning up to it, so I suppose change/time is the proof.

 

This is the fastest method I've found to learn, is to ask directly, but on every forum/site I see the rules/stickies "Research first before you ask."

 

Alright, I need to read more, speak less, I guess I get excited sometimes(sounds weird).

 

Oh man there goes that ego... ahhh.

[greenace92]

@benanamen, you're right in what you said. Today's one of those days where I slept briefly, feel really productive, but man I can't handle criticism even if it's constructive or someone just pointing out something minor. I feel this like uhh... I don't know. Gotta work on that on my part. Zen haha. I will try and post less/read more though. I get that.

[benanamen]

We don't mind helping. That's why we are here. I have no issue with you wanting to learn and asking questions when you have made some effort on the matter.

 

It just seems to me the things you are asking about is far ahead of what you need to learn before that. I will give you a real life example. My Uncle started going to flight school to learn how to fly a plane. The airplanes they use at the school are those little Cessna prop planes. He told me, at the airport, the guys who fly jets don't even talk to "Prop" guys (Propeller Pilots).

 

The point I am getting at with that is you are talking to Jet Pilots about how to you fly a jet when you haven't learned to fly the Cessna's. I have been at this a long time and know quite a bit about a lot and even I have not had to concern myself with the things you ask about.

 

Feel free to post, but everyone's time, including yours will be better served on subjects closer to your current knowledge level or what you actually need to know to solve a problem you have.

Edited May 6, 2016 by benanamen

[greenace92]

We don't mind helping. That's why we are here. I have no issue with you wanting to learn and asking questions when you have made some effort on the matter.

 

It just seems to me the things you are asking about is far ahead of what you need to learn before that. I will give you a real life example. My Uncle started going to flight school to learn how to fly a plane. The airplanes they use at the school are those little Cessna prop planes. He told me, at the airport, the guys who fly jets don't even talk to "Prop" guys (Propeller Pilots).

 

The point I am getting at with that is you are talking to Jet Pilots about how to you fly a jet when you haven't learned to fly the Cessna's. I have been at this a long time and know quite a bit about a lot and even I have not had to concern myself with the things you ask about.

 

Feel free to post, but everyone's time, including yours will be better served on subjects closer to your current knowledge level or what you actually need to know to solve a problem you have.

 

Okay, thanks for elaborating.

[gizmola]

I have written about mysql date, datetime and timestamp types in the past.

 

I thought you might be interested.  You can do range queries on any of those types.  I'm not sure there is a good reason to use seperate DATE and TIME columns when you can use a DATETIME or a TIMESTAMP.  

 

There are some advantages to using TIMESTAMP for a calendar application, when you don't need to be concerned about dates far in the past or far in the future.

 

At any rate, check these out:

 

http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

 

http://www.gizmola.com/blog/archives/93-Too-much-information-about-the-MySQL-TIMESTAMP.html

[greenace92]

I have written about mysql date, datetime and timestamp types in the past.

 

I thought you might be interested.  You can do range queries on any of those types.  I'm not sure there is a good reason to use seperate DATE and TIME columns when you can use a DATETIME or a TIMESTAMP.  

 

There are some advantages to using TIMESTAMP for a calendar application, when you don't need to be concerned about dates far in the past or far in the future.

 

At any rate, check these out:

 

http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

 

http://www.gizmola.com/blog/archives/93-Too-much-information-about-the-MySQL-TIMESTAMP.html

 

Hey thanks for the links, I'll give them a read.

 

Out of curiousity that little calendar you have on your site, did you build that or use some library/drop in code?

[gizmola]

It was a widget that is part of the code for the blog I used, which is named serendipity.  With that said, doing a calendar widget is quite easy these days given the tremendous number available, particularly those that are related to jquery.

 

See this for example:  http://www.learningjquery.com/2015/03/14-top-calendar-and-date-picker-jquery-plugins

 

In particular you might want to look at http://fullcalendar.io/ for your demo app.

[greenace92]

It was a widget that is part of the code for the blog I used, which is named serendipity.  With that said, doing a calendar widget is quite easy these days given the tremendous number available, particularly those that are related to jquery.

 

See this for example:  http://www.learningjquery.com/2015/03/14-top-calendar-and-date-picker-jquery-plugins

 

In particular you might want to look at http://fullcalendar.io/ for your demo app.

 

Wow that full calendar is nice.

 

I realize I could have just used say Google's calendar which I use Gmail a lot, have Google+ account, YouTube, etc... but I built it because I thought I could.

I was taking a Java course once in university and I couldn't get for loops, we were building a connect-four game, and I was making every single grid position by hand rather than using two forloops. So when I made this calendar with for loops to fill in the tiles, it was kind of cool.

 

Today I just noticed that the dates are off by 1, particularly compared to my local server and that of the public server. Must be using the timezone of the hosting provider... dang. Another thing to fix. ha

 

Thanks for the links.

Edited May 10, 2016 by greenace92