MySQL Forums
Forum List  »  InnoDB

Re: using enum as means of compression
Posted by: Rick James
Date: April 03, 2010 09:40AM

OK, to discuss the merits (and drawbacks) of ENUMs...
* An ENUM takes 1 byte (2 if you have > 255 possible values).
* You must specify all the possible values when the CREATE TABLE.
* Later you can ALTER TABLE to add new values to an ENUM, but this requires an amount of time proportional to the size of the table. (This may be a problem.) Bottom line: Don't plan on using ENUM for a list of values that is likely to change.
* As with all datatypes, an ENUM column could be NULL or NOT NULL. If you make it NULLable, an extra bit is needed (elsewhere) to hold that info. And you need to think about what "NULL" should mean in your app.
* If you did index the ENUM, it still might not be used. "WHERE my_enum = 'something'" is unlikely to use an index on my_enum unless 'something' occurs in less than 10-30% of the rows. (I can't give you an exact number -- the optimizer has a mind of its own. Switching from using an index to doing a table scan is usually "the right thing" when the indexed value is 'too' common.)

In contrast, a VARCHAR(99) that contains 'abcde' takes 1+5 bytes -- 1 for the length, 5 for the actual characters.

Another compression technique is to gzip (zlib / ...) long strings. For example, if you are running a blog site, it is probably beneficial to compress each comment, then use BLOB instead of TEXT. English text compresses about 3:1. Drawback: you cannot use FULLTEXT index to search the compressed blob.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: using enum as means of compression
2245
April 03, 2010 09:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.