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.