MySQL Performance issue with large number of rows
I am developing a site based on XenForo engine, and have a problem in a query that fetches all threads and joins post table and forum table to get some information for the first post and forum the thread belongs to. The query looks like:
SELECT thread . *
FROM xf_thread AS thread
INNER JOIN xf_node AS node ON (node.node_id = thread.node_id)
INNER JOIN xf_post AS post ON (post.post_id = thread.first_post_id)
WHERE thread.node_id IN ('295', '296', '297', '298', '299', '300', '301', '302', '256', '2575', '258', '259', '260', '253', '254', '255', '127', '163', '159', '144', '145', '146', '147', '148', '164', '165', '166', '167', '168', '169', '170', '162', '171', '173', '172', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '141', '142', '143', '151', '152', '153', '154', '155', '157', '156', '158', '161', '160', '149', '227', '232', '237', '233', '236', '234', '235', '238', '248', '240', '241', '242', '239', '246', '247', '243', '244', '245', '228', '229', '230', '231', '249', '250', '251', '174', '190', '195', '199', '193', '191', '197', '198', '192', '200', '204', '207', '205', '203', '206', '202', '208', '201', '187', '176', '177', '178', '189', '188', '180', '186', '184', '185', '182', '183', '181', '179', '209', '211', '217', '218', '219', '210', '212', '213', '214', '215', '216', '220', '222', '223', '224', '221', '225', '261', '291', '276', '272', '270', '265', '277', '267', '286', '292', '289', '274', '264', '287', '278', '282', '279', '281', '280', '283', '284', '285', '290', '275', '268', '263', '266', '294', '262', '293', '269', '273', '288', '271')
ORDER BY thread.last_post_date DESC
LIMIT 10
Explain results for the query are:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE node index PRIMARY PRIMARY 4 NULL 199 Using where; Using index; Using temporary; Using filesort 1 SIMPLE thread ref node_id_last_post_date,node_id_sticky_last_post_date node_id_last_post_date 4 node.node_id 221 1 SIMPLE post eq_ref PRIMARY PRIMARY 4 thread.first_post_id 1 Using index
Query takes 9+ seconds to execute.
Removing joining of xf_node table, runs the query in 0.01 seconds. Explain looks like
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE thread index node_id_last_post_date,node_id_sticky_last_post_da... last_post_date 4 NULL 69970 Using where 1 SIMPLE post eq_ref PRIMARY PRIMARY 4 thread.first_post_id 1 Using index
Removing joining of xf_post table runs the query in 0.01 seconds, explain looks like
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE thread index node_id_last_post_date,node_id_sticky_last_post_da... last_post_date 4 NULL 70840 Using where 1 SIMPLE node eq_ref PRIMARY PRIMARY 4 thread.node_id 1 Using index
So, the problem exists only when both tables are joined, but joins themselves seem to be completely correct and work perfectly separately.
Number of rows in the tables - xf_thread: 71,855, xf_node: 178, xf_post: 2,977,326
My assumption is that when both tables are joined MySQL starts to use incorrect indexes, and maybe forcing an index will solve the problem?
Your help and suggestions to find a way to resolve this problem are highly appreciated.
EDIT: Here are create table statements for all tables involved
xf_node
CREATE TABLE `xf_node` (
`node_id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(50) NOT NULL,
`description` text NOT NULL,
`node_name` varchar(50) default NULL COMMENT 'Unique column used as string ID by some node types',
`node_type_id` varbinary(25) NOT NULL,
`parent_node_id` int(10) unsigned NOT NULL default '0',
`display_order` int(10) unsigned NOT NULL default '1',
`display_in_list` tinyint(3) unsigned NOT NULL default '1' COMMENT 'If 0, hidden from node list. Still counts for lft/rgt.',
`lft` int(10) unsigned NOT NULL default '0' COMMENT 'Nested set info ''left'' value',
`rgt` int(10) unsigned NOT NULL default '0' COMMENT 'Nested set info ''right'' value',
`depth` int(10) unsigned NOT NULL default '0' COMMENT 'Depth = 0: no parent',
`style_id` int(10) unsigned NOT NULL default '0' COMMENT 'Style override for specific node',
`effective_style_id` int(10) unsigned NOT NULL default '0' COMMENT 'Style override; pushed down tree',
PRIMARY KEY (`node_id`),
UNIQUE KEY `node_name_unique` (`node_name`,`node_type_id`),
KEY `parent_node_id` (`parent_node_id`),
KEY `display_order` (`display_order`),
KEY `display_in_list` (`display_in_list`,`lft`),
KEY `lft` (`lft`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=304 ;
xf_post
CREATE TABLE `xf_post` (
`post_id` int(10) unsigned NOT NULL auto_increment,
`thread_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`username` varchar(50) NOT NULL,
`post_date` int(10) unsigned NOT NULL,
`message` mediumtext NOT NULL,
`ip_id` int(10) unsigned NOT NULL default '0',
`message_state` enum('visible','moderated','deleted') NOT NULL default 'visible',
`attach_count` smallint(5) unsigned NOT NULL default '0',
`position` int(10) unsigned NOT NULL,
`likes` int(10) unsigned NOT NULL default '0',
`like_users` blob NOT NULL,
`warning_id` int(10) unsigned NOT NULL default '0',
`warning_message` varchar(255) NOT NULL default '',
PRIMARY KEY (`post_id`),
KEY `thread_id_post_date` (`thread_id`,`post_date`),
KEY `thread_id_position` (`thread_id`,`position`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3123657 ;
xf_thread
CREATE TABLE `xf_thread` (
`thread_id` int(10) unsigned NOT NULL auto_increment,
`node_id` int(10) unsigned NOT NULL,
`title` varchar(150) NOT NULL,
`reply_count` int(10) unsigned NOT NULL default '0',
`view_count` int(10) unsigned NOT NULL default '0',
`user_id` int(10) unsigned NOT NULL,
`username` varchar(50) NOT NULL,
`post_date` int(10) unsigned NOT NULL,
`sticky` tinyint(3) unsigned NOT NULL default '0',
`discussion_state` enum('visible','moderated','deleted') NOT NULL default 'visible',
`discussion_open` tinyint(3) unsigned NOT NULL default '1',
`discussion_type` varchar(25) NOT NULL default '',
`first_post_id` int(10) unsigned NOT NULL,
`first_post_likes` int(10) unsigned NOT NULL default '0',
`last_post_date` int(10) unsigned NOT NULL,
`last_post_id` int(10) unsigned NOT NULL,
`last_post_user_id` int(10) unsigned NOT NULL,
`last_post_username` varchar(50) NOT NULL,
`prefix_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`thread_id`),
KEY `node_id_last_post_date` (`node_id`,`last_post_date`),
KEY `node_id_sticky_last_post_date` (`node_id`,`sticky`,`last_post_date`),
KEY `last_post_date` (`last_post_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=76301 ;
Thank you.
Sometimes order by clause in mysql will use temp table to sort results. For large data it can take a lot of time. Avoid using "order by desc" and sort data outside mysql.
The query you posted, does not use any column from the post table, and it is not used in filters either. I would say you could drop that.
For the DESC ordering (which is time consuming): that could be tweaked into ASC, if you create an additional column, and fill it with
MAX_INT - unix_timestamp(thread.last_post_date)
That will give you smaller numbers for later threads, so an ASC order on that gives you really a DESC on time.
In that case also new indexes have to be created.
(The example is in pseudo language, check mysql for syntax)
If the two thread columns ( node_id and first_post_id ) are not nullable and you have FOREIGN KEY constraints that you can trust, from thread(node_id) towards xf_node (node_id) and from thread(first_post_id) towards xf_post(post_id) , you can remove the joins to xf_node and xf_post :
SELECT thread . *
FROM xf_thread AS thread
WHERE thread.node_id IN (295, 296, ..., 271)
ORDER BY thread.last_post_date DESC
LIMIT 10 ;
An index on (last_post_date, node_id) might help as well, to improve efficiency:
ALTER TABLE xf_thread
ADD INDEX last_post_date_node_id
(last_post_date, node_id) ;
上一篇: 所有这些默认线程都应该运行吗？ 他们是否让我的JVM活着？
下一篇: 有大量行的MySQL性能问题