tinyint vs enum
Posted: Sat May 29, 2010 10:23 am
Not so much a thread where I'm looking for help.. just a general observation I made. Was working on a cron job script which sends messages which need to be processed to a C# program.. however the query was very slow.
Now the query itself isn't all that important, with the exception of the of the ms.postbackprocessed field, which was a tinyint(1) until this morning. This query would take over 2 seconds (with about several 100k rows in each table), which I found odd because I had all the proper indexes on all the columns that needed it. After I changed the type to enum('0','1') the query would take a small fraction of a second.. I just couldn't believe the performance difference on the index.
So I guess really my question is, how is enum (and for arguments sake lets compare enum(0,1,2,3,4,5,6,7,8,9) to tinyint(1)) so much faster than a tinyint equivelant?
Code: Select all
SELECT u1.site_userid AS from_userid,
u2.site_userid AS to_userid,
u1.site_username AS from_username,
u2.site_username AS to_username,
u1.chatuser_id AS from_chatuserid,
u2.chatuser_id AS to_chatuserid,
ms.message,
ms.message_id,
si.url,
cr.subject AS subject,
si.site_id AS site_id,
ex.serializeddata AS extradata
FROM livechat_messages AS ms
INNER JOIN livechat_chatusers AS u1 ON u1.chatuser_id = ms.chatuser_id
INNER JOIN livechat_chatrooms AS cr ON cr.chatroom_id = u1.chatroom_id
INNER JOIN livechat_chatusers AS u2 ON u2.chatroom_id = cr.chatroom_id
LEFT JOIN livechat_chatroom_extras AS ex ON cr.chatroom_id = ex.chatroom_id
INNER JOIN livechat_sites AS si ON si.site_id = cr.site_id
WHERE cr.roomtype = 'email'
AND ms.postbackprocessed = '0'
AND u2.chatuser_id != ms.chatuser_id
LIMIT 100So I guess really my question is, how is enum (and for arguments sake lets compare enum(0,1,2,3,4,5,6,7,8,9) to tinyint(1)) so much faster than a tinyint equivelant?