enum vs tinyint

[boonkit]

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'.

Assume both the col1 and col2 are indexed

Thank you

[Jeff North]

On 18 Feb 2005 01:16:24 -0800, in mailing.database.mysql
"bo*****@gmail.com" <bo*****@gmail.com> wrote:

| 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'.
|
| Assume both the col1 and col2 are indexed
|

Use a modified option 2.
colvalue enum('New', 'Maintenance', 'Renovation', 'Not Specified')
default 'New'

No need for a second table.

http://dev.mysql.com/doc/mysql/en/enum.html

---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------