We have a table in mysql with what appears to be an indexing problem. There are three daemons running and once per second run a query. The query is
SELECT * FROM albumFile WHERE status='new' OR action IS NOT NULL LIMIT 1
The problem is that three queries per second are causing mysql to use nearly 100% CPU from a simple query on a table with only about 210K rows.
Create Table: CREATE TABLE `albumFile` (
`albumId` int(10) unsigned NOT NULL default '0',
`siteId` int(10) unsigned NOT NULL default '0',
`fileId` int(10) unsigned NOT NULL default '0',
`caption` varchar(255) default NULL,
`fileOrder` int(11) NOT NULL default '0',
`status` enum('new','processing','ready') default 'new',
`action` varchar(255) default NULL,
`updateTime` int(10) default NULL,
`createDate` datetime default NULL,
PRIMARY KEY (`albumId`,`siteId`,`fileId`),
KEY `siteId` (`siteId`),
KEY `statusIdx` (`status`),
KEY `actionIdx` (`action`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
The indexes are good. Can anybody see anything that:
1) is wrong.
2) can be optimized.
3) indicates that I'm not crazy thinking this should be just fine?