tinyint vs enum

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

tinyint vs enum

Post by John Cartwright »

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.

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 100
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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: tinyint vs enum

Post by Eran »

I had all the proper indexes on all the columns that needed it
Did you check that you were actually hitting those indexes? did you run EXPLAIN on the query before and after you made the change?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: tinyint vs enum

Post by John Cartwright »

Yup. I'm not exactly a MySQL guru, but it looked ok to me.

[text]1 SIMPLE cr ref PRIMARY,site_id,roomtype roomtype 1 const 12654 Using where
1 SIMPLE si eq_ref PRIMARY PRIMARY 4 cr.site_id 1 Using where
1 SIMPLE u1 ref PRIMARY,chat_id chat_id 4 cr.chatroom_id 2
1 SIMPLE u2 ref chat_id chat_id 4 u1.chatroom_id 2 Using where
1 SIMPLE ms ref chatroom_id,postbackprocessed chatroom_id 4 u1.chatuser_id 3 Using where
1 SIMPLE ex ref chatroom_id chatroom_id 4 cr.chatroom_id 12[/text]

Sorry.. the formatting is screwy.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: tinyint vs enum

Post by Eran »

Though it appears to be selecting the correct indexes, the Extra clause for most tables is showing "Using where". You only see this when an index isn't used to resolve the WHERE condition and it is applied row-by-row.

What I think might have happened is that the table index was very fragmented after a lot of inserts/deletes. After you used an ALTER TABLE query to change the the column from TINYINT to ENUM, the index was rebuilt and the query became much faster. Using ALTER TABLE is a known fix to defragmenting tables. http://dev.mysql.com/doc/refman/5.1/en/ ... nting.html
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: tinyint vs enum

Post by John Cartwright »

pytrin wrote:Though it appears to be selecting the correct indexes, the Extra clause for most tables is showing "Using where". You only see this when an index isn't used to resolve the WHERE condition and it is applied row-by-row.

What I think might have happened is that the table index was very fragmented after a lot of inserts/deletes. After you used an ALTER TABLE query to change the the column from TINYINT to ENUM, the index was rebuilt and the query became much faster. Using ALTER TABLE is a known fix to defragmenting tables. http://dev.mysql.com/doc/refman/5.1/en/ ... nting.html
Spot on.. I changed it back to tinyint(1) and it seems to have the same performance again.

I'm not sure about why it's showing USING WHERE, since all of the used columns are indexed. Even if I remove all the WHERE condition, I am still seeing USING WHERE for the livechat_sites table. I.e.,

[text]1 SIMPLE ms ALL chatroom_id NULL NULL NULL 144394
1 SIMPLE u1 eq_ref PRIMARY,chat_id PRIMARY 4 ms.chatuser_id 1
1 SIMPLE cr eq_ref PRIMARY,site_id PRIMARY 4 u1.chatroom_id 1
1 SIMPLE u2 ref chat_id chat_id 4 u1.chatroom_id 2
1 SIMPLE ex ref chatroom_id chatroom_id 4 cr.chatroom_id 12
1 SIMPLE si eq_ref PRIMARY PRIMARY 4 cr.site_id 1 Using where[/text]

As you can see from the tables, the proper columns are indexed.

Code: Select all

CREATE TABLE IF NOT EXISTS `livechat_sites` (
  `site_id` int(10) NOT NULL auto_increment,
  `sitename` varchar(255) NOT NULL default '',
  `url` varchar(255) NOT NULL default '',
  `videosupport` tinyint(1) NOT NULL default '0',
  `maxipfreeminutes` varchar(5) NOT NULL default '-1',
  PRIMARY KEY  (`site_id`),
  KEY `videosupport` (`videosupport`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=43 ;

CREATE TABLE IF NOT EXISTS `livechat_chatrooms` (
  `chatroom_id` int(10) NOT NULL auto_increment,
  `site_id` tinyint(3) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  `active` tinyint(1) NOT NULL default '1',
  `operatoractive` tinyint(1) NOT NULL,
  `priority` tinyint(1) NOT NULL,
  `roomtype` enum('im','email') NOT NULL,
  `lastchatuser_id` int(10) NOT NULL
  `subject` text NOT NULL,
  PRIMARY KEY  (`chatroom_id`),
  KEY `active` (`active`),
  KEY `site_id` (`site_id`),
  KEY `priority` (`priority`),
  KEY `roomtype` (`roomtype`),
  KEY `operatoractive` (`operatoractive`),
  KEY `lastchatuser_id` (`lastchatuser_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=191384 ;
Thanks a ton for the insight :D
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: tinyint vs enum

Post by Eran »

Only one index can be used per table in a query. Instead of using multiple indexes, you should be using composite indexes that span multiple columns, so they can be used for more than one column in the same query. You must add them in the order they are used in the query to be effective.
http://dev.mysql.com/doc/refman/5.0/en/ ... dexes.html

In this case though, if the query is running fast enough for your tastes - there is no need to add composite indexes. you should probably remove some of the keys you create though, if they aren't used in any query.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: tinyint vs enum

Post by John Cartwright »

pytrin wrote:Only one index can be used per table in a query. Instead of using multiple indexes, you should be using composite indexes that span multiple columns, so they can be used for more than one column in the same query. You must add them in the order they are used in the query to be effective.
http://dev.mysql.com/doc/refman/5.0/en/ ... dexes.html

In this case though, if the query is running fast enough for your tastes - there is no need to add composite indexes. you should probably remove some of the keys you create though, if they aren't used in any query.
Doh.. and I knew that..

Appreciate the advise very much. Thanks!
Post Reply