enum vs tinyint
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