to vincent

Anon

Hi - you wrote:

"First, never every use smallint or tinyint in an auto_increment field. allways use int. "

why?

toma42

Because there is a limit to X digits there and it's not flexible enough if your site grows large.

e.g. I created some pages to host a max of 500 records 2 years ago but now it's multi-user and the same database has over 350,000 records where I expected 500. If I had used tinint's then I would have hit the max size my primary key field could handle long ago.

[deleted]

Exactly.

The sole reason why tinyint exists is that it uses less space in the table.

A TINYINT uses only one byte, where an INT uses four. This saves you a wopping 3 bytes per row. (everybody go to the lavatory in amazement)
This is one of MySQL's zany ways of doing things 'better' than the big guys (we all remember the reason why foreign keys are not supported in mysql?)

Unfortunately the amount of diskspace you save does not outweight the sh*t you get when your tinyint becomes too small.
So allways use an INT, everywhere.

Anon

I wouldn't say everywhere. I agree that you should never use tinyint on an auto increment field, but if you are only storing a small number, say a raiting from 1 to 10 or something like it, in a database that will hold millions of rows then those 3 bytes add up.

But yes for any auto increment / primary key field definitly do not use a tinyint.

[deleted]

Hmm.. three bytes per row, at 10 million rows thats 30 megs.
Sounds like a lot.
But, how large is your average row? certainly not 10 bytes, more like 250 bytes.
At 250 bytes per row, that one tinyint saves 1.2% of your row space.
At 10 million rows thats 30 megs out of 2.5Gigabyte.

And then I'm just leaving the page-structure of proper databases out for comfort.

Point is, it may save space, but how much space? and how do you benefit from saving that space? Do you benefit from saving that space at all?
Tinyint is quite a limit in the design, and if you ever decide you need more space, converting 10 million rows in MySQl.... nah.. let's not even try that. :-)

gid__

ALTER TABLE mytable CHANGE rating rating INT DEFAULT 0 NOT NULL;
I use tinyint's all the time personally, but only when I'm absolutely positive I'm not going to need more than 127 or 255 depending on if I made it an signed or unsigned.

Sure it might not save a whole lot, by why waste? It's not like I'm only using 2 digits to store the year or anything. 🙂

Anon

I agree with Steve here. Sure saving 30 Meg out of 2.5 Gig isn't much, but if you start thinking that you can use the largest size for anything, just incase then you go down the road to bloat. Really using your logic I could easily extrapolate that I should use a blob field to store my users first name since I may end up having a user with an extreemly long first name.

I realize that if there is the slightest chance that your data is going to go over the size of field that you've chosen then you should take the next biggest field. But if you know you will never need more then don't waste the space.

kparker

Well, perhaps that's a bit extreme. If the problem domain shows that the number of values is inherently restricted to the range 0 .. 23, there's little risk of using TinyInt. Whether it helps or hurts performance in this day and age of cheap RAM and disk is a different question, of course! :-)

[deleted]

That's the SQL statement, now see if MySQL can handle it if you run it on a 10 million row table.

"but only when I'm absolutely positive I'm not going to need more than 127 or 255"

And that's exactly my point, you can hardly ever be absolutely sure you'll never need more. And the point when you do need more is usually when you have a few thousand-thousand rows, and at that point converting it to int is something that mysql doesn't like to do.

[deleted]

You can interpret my words to mean whatever you like them to mean, but I mean them to mean that you should not choose the column type that fits your current expectations exactly. Allways leave room for expansion, because it will expand, murphy says it will, and so do the voices in my head.

BTW: Most database designs already use a blob-ish type to store the username,
remember the good old varchar(255)? Only diff between TEXT and VARCHAR is that VARCHAR can hold 255 chars max and TEXT can get a lot bigger. And when did you ever limit the size of a varchar to less than 255? It certainly doesn't make a difference to the database wether you put 10 or 100 or 255...
Everybody uses varchar because it can expand to contain things bigger than what we expect, accepting the database slowdown as a small price to pay for saving diskspace. So why use a tinyint that is so small that it can't contain my hourly fee? (hey, I can dream can't I?)

Anyway, repeating my point; You can hardly ever be sure that you'll never need more than 127/255 in the tinyint column. That is a reason why the big-boys (oracle and informix) only support the INT/NUMBER type and not smallint, tinyint etc... and they did not get that big by doing it wrong...

There, I've made my point a few times now I'll let it be.
No need to get into a personal preference battle here. :-)
I'll leave it to the reader to decide.