MySQL Forums
Forum List  »  Performance

enum vs tinyint
Posted by: Boon Kit Ho
Date: February 18, 2005 02:55AM

For instance, there is one column has values 'New', 'Maintenance', 'Renovation', 'Not Specified', and which options below is better in term of performance?

option 1.
set the column to tinyint unsigned not null, and store the value 1,2,3,4. The values 1,2,3,4 represent the values 'New', 'Maintenance', 'Renovation', 'Not Specified' respectively in another lookup table. When i want to select a list of data, the sql is
"SELECT col1, colvalue FROM tbl1 LEFT JOIN tbl2 ON (tbl1.col1 = tbl2.col2) LIMIT 0, 100".
tbl1 is main data table, tbl2 is lookup table, and both col1 and col2 column type is tinyint unsigned not null. colvalue column is 'New', 'Maintenance', 'Renovation', 'Not Specified'.

option 2
set the column to enum('n', 'm', 'r', 's'). The values 'n', 'm', 'r', 's' represent the values 'New', 'Maintenance', 'Renovation', 'Not Specified' respectively in another lookup table. When i want to select a list of data, the sql is
"SELECT col1, colvalue FROM tbl1 LEFT JOIN tbl2 ON (tbl1.col1 = tbl2.col2) LIMIT 0, 100".
tbl1 is main data table, tbl2 is lookup table, and both col1 and col2 column type isenum('n', 'm', 'r', 's'). colvalue column is 'New', 'Maintenance', 'Renovation', 'Not Specified'.

Thank you

Options: ReplyQuote


Subject
Views
Written By
Posted
enum vs tinyint
7734
February 18, 2005 02:55AM
3584
February 18, 2005 09:50AM
3447
February 22, 2005 05:25PM


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.