Does the size of the database depend on the amount of data stored in the column or on the type of data stored?
E.g: if I have Varchar column and I'm just typing like word (Hello) in it, it will take the same size if I write 255 characters in it?
Mysql – Does the size of the database depend on the amount of data stored in the column or on the type of data stored
MySQL
Related Question
- Mysql – VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed
- MySQL: Does the “length” setting affect table size in bytes
- Mysql – what effect does changing the data type of an existing indexed field have on the index
- Mysql – Which is better MySQL search strategy, varchar x 2 or text column
- Mysql – Does varchar(n) affect the number of records which can be stored per page even if you store less than n
- Mysql – Storing mail in database, data type
- Mysql – WordPress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci
- Mysql – Will the database index be more performant with a smaller varchar size
Best Answer
For your specific case the size on disk will be longer if you type more characters. This is because you defined your column as VARCHAR i.e. "varying-length character". Varying-length fields have to indicate the actual number of character present so they have an additional internal filed to show this. Therefore even empty strings consume some space when held in VARCHAR. If the column had been declared as CHAR the number of characters typed would not affect the space used. The remainder would be filled with spaces.
When looking at the bigger picture things get more complicated. Disk space is only ever consumed in chunks, often called "pages." One page is the minimum amount of disk the DB can request from the operating system and is also the minimum amount that can be read from or written to disk. Defining a table with one column of CHAR(1) and a single row will still consume a whole page of disk, typically 16kB.
Further, compression can be applied. Then the amount of space used depends on the length of the source strings and how similar they are to other strings held on that page. To be clear, the page size is still fixed (16kB) but more rows can be held on that one page compared to uncompressed storage. Then a single IO (which is typically the slowest step in query processing) will return more data, fewer IOs will be needed overall and the average query latency will reduce.
When designing tables it is best to declare the smallest size that will satisfy the actual business requirement. Don't declare them too short because those limits are hard limits and data truncation will occur. But definitely don't get lazy and use the maximum size just because it is easy. The declared sizes affect how many rows can fit on a page and so how many IOs are needed to satisfy a query. They can also affect memory usage, potentially reserving space for data which isn't actually present. Overly-generous size declarations can affect performance.