MySQL Forums :: Newbie :: Order by using filesort on where


Advanced Search

Order by using filesort on where
Posted by: Mariel Gonzales ()
Date: March 17, 2009 10:22PM

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

Options: ReplyQuote


Subject Written By Posted
Order by using filesort on where Mariel Gonzales 03/17/2009 10:22PM
Re: Order by using filesort on where Rick James 03/18/2009 08:21PM
Re: Order by using filesort on where Mariel Gonzales 03/19/2009 01:25AM
Re: Order by using filesort on where Rick James 03/19/2009 07:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.