CODEWITHSUNDEEP
mysqlsqloptimization
mmswe
mmswe

Reputation: 707

What is the most "space effective" way to store text in MySQL?

I have +10 million articles(200 to 1000 words) in a InnoDB table. I only use this type of query when I'm working with article field:

SELECT article,title,other_fields from table where id=123;

What is the most "space effective" way to store text in MySQL?

Now the table size is say 100GB, my objective is to make that as little as possible without causing too much performance tradeoff.

Upvotes: 3

Views: 191

Answers (3)

atxdba
atxdba

Reputation: 5216

As others have pointed out, innodb compressed rows is an option.

You need to be sure that your file_format system parameter is set to Barracuda though. An attempt to alter to make the row format compressed where row format is the default Antelope may result in a wasted table rebuild only to give you a warning that row format wasn't supported.

I've found the compression on various datasets to not be very good and give a lot of cpu overhead, especially hurting performance where joins are involved.

I've been experimenting to the TokuDB storage engine for mysql which seems to offer better compression ratios at better speeds, among other benefits (such as quick online schema changes).

Upvotes: 2

Shomz
Shomz

Reputation: 37701

Depending on the content, you might get some good/useful results using a clever lossless compression algorithm. Other than that, you might group texts into batches, and compress that altogether for much better results without a significant performance drop.

It's all about the space/speed proportion.

Upvotes: 0

user3722246
user3722246

Reputation:

MyISAM is more space friendly than InnoDB, you can start with that one.

Upvotes: 1

Related Questions