MySQL: store rare long string [duplicate]

Question

There is a MySQL table:

CREATE TABLE `variables` ( `name` VARCHAR(50) NOT NULL, `value` VARCHAR(250) NOT NULL, PRIMARY KEY (`name`) ) ENGINE=InnoDB CHARSET=utf8mb4;

There is only one type of SELECT: select one row by the key.

value is usually short (in most cases less than 10 characters). But under new conditions it is in very rare cases (<1%) it can be long (about 10 Kb). How should I change the table?

As I understand VARCHAR(15000) is more efficient in this case than TEXT. But what about this solution?

CREATE TABLE `variables` ( `name` VARCHAR(50) NOT NULL, `value` VARCHAR(250) NULL, `long_value` TEXT NULL, PRIMARY KEY (`name`) ) ENGINE=InnoDB CHARSET=utf8mb4;

If the value is short then save it to the value. A long value save to long_value.

After SELECT value,long_value FROM variables WHERE name=? check which column is not NULL. Or just SELECT IFNULL(value, long_value) AS value WHERE variables WHERE name=?.

There is only one type of SELECT: select one row by the key. I think that the root of the unefficiency is VARCHAR(50) column which is used as primary key and searching criteria. — Akina
– Akina, Commented Jan 7, 2024 at 12:31
Use TEXT for value and drop the long_value column. MySQL is already doing well, what you try to do. The overhead of TEXT is 2 bytes. The overhead of VARCHAR(250) (with utf8mb4) is also 2 bytes. The way the values are stored is the same. — Paul Spiegel
– Paul Spiegel, Commented Jan 7, 2024 at 18:04

slaakso · Accepted Answer · 2024-01-07 11:50:57Z

Use the VARCHAR(15000) for the data. The space taken in the DB is the actual data length + few bytes of overhead. This will work for both the short and long strings.

Separating the data into two fields just creates additional complexity in your application and actually wastes space.

Collectives™ on Stack Overflow

MySQL: store rare long string [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related