我如何用group by加速mysql查询

I have simple MyISAM table for search, where I indexed fulltext search ion "search_text" for search text

我有简单的MyISAM搜索表，我在其中索引全文搜索“search_text”搜索文本

table structure :

表结构：

CREATE TABLE IF NOT EXISTS `product_search` (
  `dept_id` int(11) unsigned NOT NULL DEFAULT '0',
  `product_id` int(11) NOT NULL DEFAULT '0',
  `search_text` longtext,
  FULLTEXT KEY `search` (`search_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Query :

查询：

SELECT p.product_id
      , p.dept_id
 FROM product_search p
 WHERE (MATCH(search_text) AGAINST('fathers day') AND 1 )
 GROUP BY product_id LIMIT 50

With group by it took around 10-12 seconds

随着小组大约花了10-12秒

Without group by it took 0.0086 seconds

没有组，花了0.0086秒

When I have checked the query with explain query explain :

当我用解释查询检查查询时解释：

id  select_type   table      type       possible_keys    key    key_len     ref     rows    Extra   
1   SIMPLE        p  fulltext   search          search   0                   1      Using where; Using temporary; Using filesort

What should I do in this case?

在这种情况下我该怎么办？

1 个解决方案

#1

Have you tried by Indexing product_id field?

您是否通过Indexing product_id字段尝试过？

CREATE INDEX idx_product_id  ON product_search (product_id);

Hope this will increase the query performance. Thank you.

希望这会增加查询性能。谢谢。

#1