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
