… options for ENUM or SET columns. The tricky bit is that it’s unique intervals, not actual intervals, so two ENUM columns both having…, so while you can have many more ENUM(‘Y’,'N’) columns, you can only have 255 ENUM columns with unique values. If you were… (REC_MAX_N_FIELDS – DATA_N_SYS_COLS * 2) Which adds up to (1024-1)-3*2=1017 which is exactly…
Post: Picking datatype for STATUS fields
August 9, 2008 By 24 Comments
… which is not allowed by ENUM list it will be always converted to ” – empty string, the additional ENUM columns can have. The other gotcha is sort order which is done for ENUM in the order in… specified in the ENUM declaration – not according to their string values. So make sure to add values specify strings for ENUM in sorted…
Comment: Hacking to make ALTER TABLE online for certain changes
October 30, 2007 By 26 Comments
… require a table rebuild. Likewise, a change of an ENUM column to add more ENUM values shouldn’t require one provided the layout of…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
… ”, `country_id` smallint(5) unsigned NOT NULL DEFAULT ’0′, `gender` enum(‘M’,'F’) NOT NULL DEFAULT ‘M’, `account_type` varchar(32… `state_id_idx` (`state_id`) by two columns: ALTER TABLE userinfo DROP KEY state_id, ADD KEY `state_id_2` (state_id… difference will be less. MyISAM uses key-compression for VARCHAR columns, so makes things worse). So to have good performance for…
Comment: Picking datatype for STATUS fields
August 11, 2008 By 24 Comments
… the start. I would add that using int types works well because it’s portable–more so than enums, anyway. As a follow-up, How do you feel about indexes on status columns? Typically, there are very few values, and we’re trained to only index columns with readonable cardinality. I see a lot of cases though…
Post: Intro to OLAP
July 12, 2010 By 10 Comments
…defined in dimension tables. The fact table contains columns which are joined to the dimension tables, such…dimension. A degenerate dimension is stored as an ENUM in many cases. In the example below that…two different order statuses. Such a dimension would add an extra join, which is expensive. Any …
