Order by using filesort on where
Hi,
I'm a newbie on myqsl and I would like to ask how can I resolve my problems based on what I have seen on the mysql slow_log. below is the log that I collected.
In the slowquery logs for this server this came out.
# Time: 090317 20:50:39
# User@Host: dba:host 127.0.0.1
# Query_time: 11 Lock_time: 0 Rows_sent: 100 Rows_examined: 35143
SELECT ms_id, r_id, gsm_num, access_code, msg, header, bindata, dcs, status, operator, tariff, err_code, svc_type,callback_url from out_p where status = 0 and ms_id > 255109 and r_id = 3 order by ms_id limit 100;
my explain plan
explain SELECT ms_id, r_id, gsm_num, access_code, msg, header, bindata, dcs, status, operator, tariff, err_code, svc_type,callback_url from out_p where status = 0 and ms_id > 255109 and bfr_id = 3 order by ms_id limit 100;
id: 1
select_type: SIMPLE
table: sms_out
type: ref
possible_keys: PRIMARY,index_buffer_id,index_status
key: index_status
key_len: 1
ref: const
rows: 1
Extra: Using where; Using filesort
1 row in set (0.00 sec)
Create Table
CREATE TABLE `out_p` (
`ms_id` int(12) unsigned NOT NULL AUTO_INCREMENT,
`r_id` tinyint(2) NOT NULL DEFAULT '0',
`gsm_num` varchar(15) NOT NULL DEFAULT '',
`access_code` varchar(20) DEFAULT NULL,
`msg` text,
`status` tinyint(2) NOT NULL DEFAULT '0',
`header` text,
`bindata` text,
`dcs` int(3) DEFAULT NULL,
`in_id` int(12) unsigned DEFAULT NULL,
`pcode` varchar(50) DEFAULT NULL,
`operator` varchar(6) NOT NULL DEFAULT '',
`tariff` varchar(24) DEFAULT NULL,
`err_code` varchar(6) DEFAULT NULL,
`msg_ref` varchar(30) DEFAULT NULL,
`msg_seq` int(12) DEFAULT NULL,
`svc_type` varchar(24) DEFAULT NULL,
`datein` date NOT NULL DEFAULT '0000-00-00',
`timein` time NOT NULL DEFAULT '00:00:00',
`datesent` date NOT NULL DEFAULT '0000-00-00',
`timesent` time NOT NULL DEFAULT '00:00:00',
`callback_url` text,
PRIMARY KEY (`ms_id`),
KEY `index_buffer_id` (`r_id`),
KEY `index_status` (`status`),
KEY `index_datein` (`datein`),
KEY `index_timein` (`timein`),
KEY `index_datesent` (`datesent`),
KEY `index_timesent` (`timesent`),
KEY `index_pcode` (`pcode`),
KEY `gsm_num` (`gsm_num`),
KEY `index_msg_ref` (`msg_ref`),
KEY `index_msg_seq` (`msg_seq`)
) ENGINE=MyISAM AUTO_INCREMENT=1346763 DEFAULT CHARSET=latin1 |
These query takes longer than using a query without the order by clause.
Please help How can I resolve this one. Or should I remove the order by clause instead. What would be the effect.
Best regards,
maye