mysql score match_mysql – 使用ORDER BY和MATCH AGAINST优化选择查询

以下查询需要25秒,有2个表连接.第一个帖子表有150,00行,主题表有50,000行.任何人都知道如何加快速度.

SELECT SQL_NO_CACHE

post_search.post_id,

topic_search.topic_id,

topic_search.topic_title,

topic_search.topic_last_post_time,

MATCH(post_search.post_text,topic_search.topic_title) AGAINST('search_terms' IN BOOLEAN MODE) AS score

FROM bb_posts_fulltext_search post_search

LEFT JOIN bb_topics_fulltext_search topic_search

ON post_search.topic_id = topic_search.topic_id

WHERE MATCH(post_search.post_text,topic_search.topic_title) AGAINST('search_terms' IN BOOLEAN MODE)

GROUP BY topic_search.topic_id

ORDER BY score DESC

LIMIT 0,6

描述

mysql> DESCRIBE bb_posts_fulltext_search;

+-----------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+------------+------+-----+---------+-------+

| post_id | bigint(20) | NO | PRI | NULL | |

| post_text | longtext | YES | MUL | NULL | |

| topic_id | bigint(20) | YES | MUL | NULL | |

+-----------+------------+------+-----+---------+-------+

描述

mysql> DESCRIBE bb_topics_fulltext_search

-> ;

+----------------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------------------+--------------+------+-----+---------+-------+

| topic_id | int(11) | NO | PRI | NULL | |

| topic_title | varchar(255) | YES | MUL | NULL | |

| topic_posts | bigint(20) | YES | | NULL | |

| topic_poster_name | varchar(40) | YES | | NULL | |

| topic_last_post_id | bigint(20) | YES | | NULL | |

| forum_id | int(11) | YES | | NULL | |

| parent_group_id | int(11) | YES | | NULL | |

| child_group_id | int(11) | YES | | NULL | |

| topic_last_post_time | datetime | YES | MUL | NULL | |

+----------------------+--------------+------+-----+---------+-------+

说明

+----+-------------+--------------+--------+---------------+---------+---------+----------------------------------+--------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+--------+---------------+---------+---------+----------------------------------+--------+---------------------------------+

| 1 | SIMPLE | post_search | ALL | NULL | NULL | NULL | NULL | 158972 | Using temporary; Using filesort |

| 1 | SIMPLE | topic_search | eq_ref | PRIMARY | PRIMARY | 4 | wordpress.post_search.topic_id | 1 | Using where |

+----+-------------+--------------+--------+---------------+---------+---------+----------------------------------+--------+---------------------------------+

更新：索引

+--------------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| bb_posts_fulltext_search | 0 | PRIMARY | 1 | post_id | A | 158972 | NULL | NULL | | BTREE | | |

| bb_posts_fulltext_search | 1 | topic_id | 1 | topic_id | A | 52990 | NULL | NULL | YES | BTREE | | |

| bb_posts_fulltext_search | 1 | post_text | 1 | post_text | NULL | 1 | NULL | NULL | YES | FULLTEXT | | |

+--------------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

创建1

DROP TABLE IF EXISTS `wordpress`.`bb_posts_fulltext_search`;

CREATE TABLE `wordpress`.`bb_posts_fulltext_search` (

`post_id` bigint(20) NOT NULL,

`post_text` longtext,

`topic_id` bigint(20) DEFAULT NULL,

PRIMARY KEY (`post_id`),

KEY `topic_id` (`topic_id`),

FULLTEXT KEY `post_text` (`post_text`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

创造2

DROP TABLE IF EXISTS `wordpress`.`bb_topics_fulltext_search`;

CREATE TABLE `wordpress`.`bb_topics_fulltext_search` (

`topic_id` int(11) NOT NULL,

`topic_title` varchar(255) DEFAULT NULL,

`topic_posts` bigint(20) DEFAULT NULL,

`topic_poster_name` varchar(40) DEFAULT NULL,

`topic_last_post_id` bigint(20) DEFAULT NULL,

`forum_id` int(11) DEFAULT NULL,

`parent_group_id` int(11) DEFAULT NULL,

`child_group_id` int(11) DEFAULT NULL,

`topic_last_post_time` datetime DEFAULT NULL,

PRIMARY KEY (`topic_id`),

KEY `topic_last_post_time` (`topic_last_post_time`),

FULLTEXT KEY `topic_title` (`topic_title`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;