2

Forgive me if I'm not using the appropriate vocabulary.

I'm planning on implementing a MySQL database with a PHP front end so I can enter work order details and files (screengrabs, ZIPs, etc.) into said database. As it is now, my previous work orders are saved in email PSTs and it's inconvenient to mount them one at a time to review and search for previous work orders.

The description on a work order can be a short sentence up to many thousands of characters. If I have columns as VARCHAR(i) or TEXT/MEDIUMTEXT data types, will the maximum space be allocated to each field or will the database grow the fields up to the capacity allowed by the data type?

I suppose a visual way to ask this question would be, is my text field a cup having an immutable volume with a variable amount of water or is it a balloon having an expanding volume of air?

Improve this question

1 Answer 1

Reset to default
2

It's a balloon. If you only put a couple bytes worth of text in the field, that "cell" will only take up a couple bytes (plus whatever the cost of the cell structure is). This is assuming you're using one of the more common engines.

Cell structure can change in size though; this isn't a zero-sum game. A LONGTEXT column will take up 4 bytes per column per row. A VARCHAR will take up only 1 byte per column per row. These bytes are used to describe the length of the data in the actual cell. Now days with storage being so cheap, it doesn't really matter unless you're talking about hundreds of millions of rows. 1 million rows of an empty LONGTEXT will use 3.82MB and 1 million rows of an empty VARCHAR will use 0.95MB

Improve this answer
1
  • 1
    Thanks. That's what I was hoping for. I'm not sure of the engine just yet. I've just used the default suggested by the workbench when I established the other databases on the server. I think it's InnoDB.
    – user38537
    Oct 31, 2015 at 7:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .