CODEWITHSUNDEEP
mysql
We're All Mad Here
We're All Mad Here

Reputation: 1554

MySQL reduce varchar index size?

I have a table with a varchar(255) column. I perform A LOT of searches based on this column, so I need fast selects. That's why I used that varchar as primary key, but this doubles the table's size.

I there any way around this? I understand indexes increase the column size but doubling it is just ridiculous!

Is it possible to store only the 1st 10 bytes of the varchar in the index and sightly increase the select time by heavily reducing the size?

CREATE TABLE data_storage(
id TINYINT UNSIGNED NOT NULL,
data VARCHAR(255) CHARACTER SET utf8 NOT NULL,
no INT UNSIGNED NOT NULL,
UNIQUE KEY(id, data), PRIMARY KEY(id, no)) ENGINE = INNODB

Upvotes: 3

Views: 866

Answers (1)

Ja͢ck
Ja͢ck

Reputation: 173522

The fact that you make many searches on a column shouldn't be the reason that you're making it a primary key, or that it is in fact a good candidate.

It's not useful to create something like a partially indexed primary key, because of the uniqueness constraint; however, you can create a partial index:

ALTER TABLE `tablename` ADD INDEX(`colname`(10));

Then, either remove the primary key or find a better one, assuming you have no foreign keys pointing to this table.

If you do have foreign keys pointing to this table, it would be best to create a surrogate key, such as an auto incremented integer column.

Upvotes: 7

Related Questions