The following query takes 25 seconds with two table joins. The first message table has 150.00 rows, the topic table contains 50,000 rows. Does anyone know how I can speed this up.
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
DESCRIBE
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 | | +-----------+------------+------+-----+---------+-------+
DESCRIBE
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 | | +----------------------+--------------+------+-----+---------+-------+
EXPLAIN
+----+-------------+--------------+--------+---------------+---------+---------+----------------------------------+--------+---------------------------------+ | 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 | +----+-------------+--------------+--------+---------------+---------+---------+----------------------------------+--------+---------------------------------+
UPDATE: indexes
+--------------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | 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 | | | +--------------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
CREATE 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;
CREATE 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;
SOLUTION: http://forums.mysql.com/read.php?115,418955,418955#msg-418955