Ask MySQL Geek LJ...

[penk]

Okay LJ folks. Riddle me this one:

select * from queue_encoder
where
status='ready' and
machine_name IS NULL
order by
urgent_flag desc ,
timestamp_enqueued DESC
limit 10

yacht-2:~ dbs$ time mysql -uroot cms < urgentfirst.sql
[...]
real 0m0.037s

explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE queue_encoder range index_1,index_3,compound_1,compound_2 compound_2 154 NULL 3377962 Using where


---------------

Now if I add a constraint:
select * from queue_encoder
where
status='ready' and
machine_name IS NULL AND
codec_id IN (4,9,10,11)
order by
urgent_flag desc ,
timestamp_enqueued DESC
limit 10

yacht-2:~ dbs$ time mysql -uroot cms < urgentfirst.sql
[...]
real 0m24.982s

explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE queue_encoder ref index_1,index_3,compound_1,compound_2 compound_2 154 const,const 1576779 Using where


CREATE TABLE `queue_encoder` (
`queue_encoder_id` int(11) NOT NULL AUTO_INCREMENT,
`media_type_id` int(11) NOT NULL DEFAULT '0',
`item_id` int(11) NOT NULL DEFAULT '0',
`label_id` int(11) NOT NULL DEFAULT '0',
`codec_id` int(11) DEFAULT NULL,
`urgent_flag` int(1) NOT NULL DEFAULT '0',
`machine_name` varchar(50) DEFAULT NULL,
`process_id` int(2) DEFAULT NULL,
`timestamp_enqueued` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`timestamp_started` datetime DEFAULT NULL,
`timestamp_finished` datetime DEFAULT NULL,
`status` enum('ready','success-complete','success-incomplete','error','in-process','delay') NOT NULL DEFAULT 'ready',
`status_message` varchar(255) DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`queue_encoder_id`),
UNIQUE KEY `unique_1` (`item_id`,`media_type_id`,`codec_id`),
KEY `index_1` (`machine_name`,`process_id`,`status`),
KEY `index_2` (`timestamp_enqueued`,`label_id`,`codec_id`,`media_type_id`),
KEY `index_3` (`status`,`machine_name`,`codec_id`,`media_type_id`,`label_id`),
KEY `compound_1` (`status`,`machine_name`,`codec_id`,`media_type_id`,`label_id`,`urgent_flag`,`timestamp_enqueued`),
KEY `compound_2` (`status`,`machine_name`,`urgent_flag`,`timestamp_enqueued`)
) ENGINE=MyISAM AUTO_INCREMENT=74076324 DEFAULT CHARSET=utf8 |

(Background - this table has 3.4 million rows-ish in it. We have a process that asks the following select question about every 30 seconds:

SELECT * from queue_encoder
WHERE
status = 'ready' AND
machine_name IS NULL AND
codec_id IN (4,9,10,11) AND
media_type_id IN (1,2) AND
label_id IN (2,3,4,11,13,15,17,18,19,20,21,22,29,30,46,47)
ORDER BY
urgent_flag DESC,
timestamp_enqueued DESC
limit 20

That query, due to ISAM table locking, can take 2-4 minutes to complete (it's actually an UPDATE...WHERE clause). If I can make that be fully indexed, we will significantly increase our processing throughput.

Help?