I plan to be storing strings that have a maximum size of 4,500 VarChar, but MOST of the entries will be under 200 characters. Is MySql smart enough to optimize?
My current solution is to use 5 tables, data_small, data_medium, data_large, etc and insert based on the length of the string. The other solution would be to save files to disk, which would mean a second hit to the database, but result in a smaller return.
MySQL would do fine as would most every RDBMS for that matter. When you specify a field as type CHAR() the number of characters is always used regardless of how many characters are in your string. For instance: If you have Char(64) field and you insert 'ABCD' then the field is still 64 bytes (assuming non-unicode).
When using VARCHAR(), however, the cell only uses as many bytes as are in the string, plus the number of bytes necessary to store the size of the string. So: If you have VARCHAR(64) and insert 'ABCD' you will only use 5 bytes. 4 for the characters 'ABCD' and one for the number of characters '4'.
Your extremely varying string lengths are exactly the reason we have VARCHAR(), so feel free to use VARCHAR(4500) and rest assured you will only be using as much space as necessary to store the characters in the string, and a little bit extra for the length.
Somewhat related: This is why it's not a great idea to use VARCHAR() for fields that don't have varying length strings being inserted. You are wasting space storing the size of the string when it's already known. For instance, telephone numbers of the form x-xxx-xxx-xxxx should just use Char(14) since it will always take up 14 characters and only 14 bytes are necessary. If you used VARCHAR(14) instead you would actually end up using 15 bytes.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加