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.
Subject
Views
Written By
Posted
3888
March 28, 2010 09:40AM
1428
March 29, 2010 10:47PM
1384
April 02, 2010 02:27AM
1279
April 02, 2010 06:41AM
1251
April 03, 2010 12:54AM
1329
April 02, 2010 07:32PM
1451
April 03, 2010 01:10AM
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.