[RESOLVED] Will MYSQL auto change the datatype?

Sayian

For instance ..

Lets say i set a primary key , user_id , small_int(4)

Then what happens when my user count gets to 10000

Thats 5 numbers now, not 4, so will mysql fail to enter the 10000th user, or will it auto update the data_type to a larger number automatically?

thorpe

it will fail to enter the value.

Sayian

So how are you supposed to optimize data types then?

If i had 30 tables in a database, with user_id as a small_int(4)

Id have to change ALL those data types when i got my 10000th user or my site would break?

That doesnt seem like its a very efficent way to handle information types?

veridicus

Generally it is efficient enough. It's the designer's job to pick field sizes that are adequate for growth. Also, making an int field wider than necessary generally won't hurt performance. If you have "wide" tables you'll want to make sure you don't use up too much space. But making an int(8) where today you only need int(4) won't make any noticable difference in performance. Obviously with ID fields that small you don't have millions of records in your tables anyway.

Sxooter

I believe you all are misunderstaning the purpose of that (4) on an integer type in mysql.

mysql> create table i (i int(4));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into i values (100000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from i;
+--------+
| i      |
+--------+
| 100000 |
+--------+
1 row in set (0.01 sec)

The (4) in mysql is NOT A LIMIT ON SIZE!

From the docs:

Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional display width specification is used to left-pad the display of values having a width less than the width specified for the column.

laserlight

That doesnt seem like its a very efficent way to handle information types?

Possibly, but it is standard. If you wish for a non-standard but more space efficient implementation, you could choose SQLite instead, since its author disagrees with that part of the SQL standard.

The (4) in mysql is NOT A LIMIT ON SIZE!

Ah, but SMALLINT is a limit on the size.

Sxooter

Sayian wrote:
So how are you supposed to optimize data types then?

If i had 30 tables in a database, with user_id as a small_int(4)

Id have to change ALL those data types when i got my 10000th user or my site would break?

That doesnt seem like its a very efficent way to handle information types?

Assuming you were using a database with constraints, you could have a constraint like so:

test2=> create table z(i int check (i<10000));
CREATE TABLE
test2=> insert into z values (10001);
ERROR:  new row for relation "z" violates check constraint "z_i"
test2=> alter table z drop constraint z_i;
ALTER TABLE
test2=> insert into z values (10001);
INSERT 2106748451 1

You should make your base types big enough for growth. Resticting them to some arbitrary smaller size like tinyint (or smallint ed...) saves you VERY little in performance terms, but costs you hugely in maintenance at a later date. Just go with int or bigint for most of your int types.

Remember the first rule of performance tuning: Don't