MySQL Forums :: Performance :: Slow query with order by and limit


Advanced Search

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 3775 Andrey Petukhov 12/16/2010 07:43AM
Re: Slow query with order by and limit 1168 Thomas Wiedmann 12/17/2010 12:55AM
Re: Slow query with order by and limit 1604 Andrey Petukhov 12/17/2010 05:43AM
Re: Slow query with order by and limit 957 Thomas Wiedmann 12/17/2010 05:59AM
Re: Slow query with order by and limit 880 Andrey Petukhov 12/17/2010 07:58AM
Re: Slow query with order by and limit 913 Thomas Wiedmann 12/17/2010 08:20AM
Re: Slow query with order by and limit 954 Rick James 12/17/2010 09:04AM
Re: Slow query with order by and limit 1047 Andrey Petukhov 12/17/2010 09:29AM
Re: Slow query with order by and limit 899 Rick James 12/17/2010 09:53AM
Re: Slow query with order by and limit 884 Andrey Petukhov 12/17/2010 10:04AM
Re: Slow query with order by and limit 1305 Rick James 12/17/2010 11:47PM
Re: Slow query with order by and limit 958 Andrey Petukhov 12/18/2010 12:39AM
Re: Slow query with order by and limit 953 Rick James 12/18/2010 10:50AM
Re: Slow query with order by and limit 749 Andrey Petukhov 12/20/2010 04:46AM
Re: Slow query with order by and limit 789 Andrey Petukhov 12/17/2010 09:31AM


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.