MySQL Forums
Forum List  »  Performance

Slow query with order by and limit
Posted by: Andrey Petukhov
Date: December 16, 2010 07:43AM

Please help me to optimize slow query:

Select node.title inner join term_node on node.vid=term_node.vid
where term_node.tid=10
order by node.created limit 0,3

I'm getting "Using filesort" on this query with 10k-40k rows.
Complete info below:

SHOW CREATE TABLE node

CREATE TABLE `node` (
 `nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `vid` int(10) unsigned NOT NULL DEFAULT '0',
 `type` varchar(32) NOT NULL DEFAULT '',
 `language` varchar(12) NOT NULL DEFAULT '',
 `title` varchar(255) NOT NULL DEFAULT '',
 `uid` int(11) NOT NULL DEFAULT '0',
 `status` int(11) NOT NULL DEFAULT '1',
 `created` int(11) NOT NULL DEFAULT '0',
 `changed` int(11) NOT NULL DEFAULT '0',
 `comment` int(11) NOT NULL DEFAULT '0',
 `promote` int(11) NOT NULL DEFAULT '0',
 `moderate` int(11) NOT NULL DEFAULT '0',
 `sticky` int(11) NOT NULL DEFAULT '0',
 `tnid` int(10) unsigned NOT NULL DEFAULT '0',
 `translate` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`nid`),
 UNIQUE KEY `vid` (`vid`),
 UNIQUE KEY `vid_created` (`vid`,`created`),
 KEY `node_changed` (`changed`),
 KEY `node_created` (`created`),
 KEY `node_moderate` (`moderate`),
 KEY `node_promote_status` (`promote`,`status`),
 KEY `node_status_type` (`status`,`type`,`nid`),
 KEY `node_title_type` (`title`,`type`(4)),
 KEY `node_type` (`type`(4)),
 KEY `uid` (`uid`),
 KEY `tnid` (`tnid`),
 KEY `translate` (`translate`)
) ENGINE=MyISAM AUTO_INCREMENT=301489 DEFAULT CHARSET=utf8

SHOW CREATE TABLE term_node

CREATE TABLE `term_node` (
 `nid` int(10) unsigned NOT NULL DEFAULT '0',
 `vid` int(10) unsigned NOT NULL DEFAULT '0',
 `tid` int(10) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`tid`,`vid`),
 KEY `vid` (`vid`),
 KEY `nid` (`nid`),
 KEY `tid` (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

SHOW TABLE STATUS LIKE 'node'

Name 	Engine 	Version 	Row_format 	Rows 	Avg_row_length 	Data_length 	Max_data_length 	Index_length 	Data_free 	Auto_increment 	Create_time 	Update_time 	Check_time 	Collation 	Checksum 	Create_options 	Comment
node 	MyISAM 	10 	Dynamic 	172816 	170 	29426660 	281474976710655 	47495168 	0 	301490 	2010-12-16 18:00:02 	2010-12-16 18:28:07 	2010-12-16 18:00:07 	utf8_general_ci 	NULL


SHOW TABLE STATUS LIKE 'term_node'

Name 	Engine 	Version 	Row_format 	Rows 	Avg_row_length 	Data_length 	Max_data_length 	Index_length 	Data_free 	Auto_increment 	Create_time 	Update_time 	Check_time 	Collation 	Checksum 	Create_options 	Comment
term_node 	MyISAM 	10 	Fixed 	183476 	13 	2385188 	3659174697238527 	8467456 	0 	NULL 	2010-12-16 14:29:55 	2010-12-16 18:28:07 	2010-12-16 14:29:56 	utf8_general_ci 	NULL

EXPLAIN SELECT node.nid AS nid, node.created AS node_created, node.title AS node_title
FROM node node
INNER JOIN term_node term_node ON node.vid = term_node.vid
WHERE
node.type IN ('news')
AND node.STATUS <>0
AND term_node.tid =10
ORDER BY node_created DESC
LIMIT 0 , 3

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	term_node 	ref 	PRIMARY,vid,tid 	PRIMARY 	4 	const 	20103 	Using index; Using temporary; Using filesort
1 	SIMPLE 	node 	eq_ref 	vid,vid_created,node_status_type,node_type 	vid 	4 	uralpress.term_node.vid 	1 	Using where


SHOW VARIABLES LIKE '%buffer%'

Variable_name 	Value
bulk_insert_buffer_size 	8388608
join_buffer_size 	131072
key_buffer_size 	134217728
myisam_sort_buffer_size 	8388608
net_buffer_length 	8192
preload_buffer_size 	32768
read_buffer_size 	262144
read_rnd_buffer_size 	524288
sort_buffer_size 	16777216
sql_buffer_result 	OFF



Edited 2 time(s). Last edit at 12/16/2010 08:09AM by Andrey Petukhov.

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query with order by and limit
3896
December 16, 2010 07:43AM
969
December 17, 2010 09:04AM
923
December 17, 2010 09:53AM
1329
December 17, 2010 11:47PM
982
December 18, 2010 10:50AM


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.