[SOLVED] best types and sizes

[regdude]

Hi,

I have been googling for this question for a while now and I found few answers for few situations.

Now my IP is stored as INT, not char, and seems to be bit faster and I encountered with a new atribute - UNSIGNED.

So the unsigned atribute can't store negative numbers, but what happens if an UPDATE or INSERT gives a negative number?

What should be the best type and size for a number with 10 digits (positive), the best for numbers 1 or 0 and the best for a text with about 50 chars?

[dzelenika]

If your table don't contains millions of rows it is irrelevant.

I prefer using data format which is easier to handle (VARCHAR for IP address, INT for integer numbers CHAR(1) for yes/no, status, active or similar values and off course VARCHAR for text

 

[regdude]

The table is quite large, it has about 50 colums and over 1000 rows (will be larger), so every milisecond counts.

[fenway]

The table is quite large, it has about 50 colums and over 1000 rows (will be larger), so every milisecond counts.

Speed has nothing to do with column type choice.

 

What happens with an out-of-range value depends on your mysql version and your choice of sql mode.

 

Storing IP addresses requires INT UNSIGNED.

 

10 digits ~ 10M; MEDIUMINT UNSIGNED goes up to ~16M, but I'd stick with INT UNSIGNED regardless.

 

0/1 can use a BIT field, though I'm always against storing just a binary flag, since that's NEVER sufficient.

 

As for 50 (single-byte) characters, your choices are CHAR(50) or VARCHAR(50), which really depends on your data distribution.

[regdude]

Well, I would like to doubt that.

A while go I made the whole script in a total mess, but a week ago I did a major recode.

What I did was simply change from checking name to checking ID and some other things, like shortening the code (less instructions), I managed to get the query time from 1.2s to 0.2s!

After you gave me the bit-type to test, I found in google an article "why I shouldn't use BIT", so I better will not.

So what should be better?

Can I specify and action for an out-of-range value on a single column? Or that is only possible with every column?

What is the default action?

[fenway]

Sure, finding numbers is faster than finding text -- but that's not column type choice now, is it?  That's application logic in deciding which field(s) to search and/or index.

 

And I too recommended against BIT -- but only because it's probably going to change later.  If you know for certain it will only ever store 0 and 1, then BIT *is* the correct type.

 

As for out-of-range, mysql will either issue a warning or an error, depending on the sql_mode.  How you decide to handle those is your choice... but there's nothing more mysql can do for you.