MySQL查询速度慢，全文索引

I have 1 MyISAM table with 620,000 rows. Im running XAMPP on a Dual Core Server with 2GB RAM. Apache is installed as a Windows Service, MySQL is controlled from the XAMPP control panel.

我有一个有62万行的ismyam表。我在一个具有2GB内存的双核心服务器上运行XAMPP。Apache是作为Windows服务安装的，MySQL是由XAMPP控制面板控制的。

The query below is taking 30+ seconds to run.

下面的查询运行需要30秒以上。

select  `id`,`product_name`,`search_price`,`field1`,`field2`,
       `field3`,`field4`
    from  `all`
    where  MATCH (`product_name`) AGAINST ('searchterm')
      AND  `search_price` BETWEEN 0 AND 1000
    ORDER BY  `search_price` DESC
    LIMIT  0, 30

I have a FULLTEXT index on product_name, a BTREE on search_price, auto increment on id

我在product_name上有一个全文索引，在search_price上有一个BTREE，在id上有一个自动增量

If I explain the above query the results are:

如果我解释上述查询，结果是:

id select_type   table type     possible_keys                       key                   key_len ref rows Extra
1  SIMPLE        all   fulltext search_price,FULLTEXT_product_name  FULLTEXT_product_name 0 NULL 1 Using where; Using filesort

How can I speed up this query? Should it be taking this long on a table of 620,000 rows?

如何加快查询速度?在一个62万行的表上花费这么长的时间吗?

Ive just noticed that this only happens when the database has not been queried for a while, so im guessing this is to do with the cache, the first query is taking 30+ seconds, then if I try a second time the query takes 1 second

我刚刚注意到，这种情况只发生在数据库暂时未被查询时，所以我猜测这与缓存有关，第一个查询需要30多秒，然后如果我尝试第二次查询需要1秒

2 个解决方案

#1

MySQL's capability of dealing with FULLTEXT is somewhat limited when th size of the table goes above 300,000. And it will peform even worse if you use really common words as search keywords like (in,the,of, etc commonly marked as stop words). I recommend using Sphinx Full Text Search/ Apache Lucene

当表的大小超过30万时，MySQL处理全文的能力是有限的。如果你使用真正常用的词作为搜索关键字(in,the,of，等等，通常标记为stop words)，情况会更糟。我建议使用Sphinx全文搜索/ Apache Lucene

* links:

*链接:

Comparison of the two

比较这两个

More Comparison

更多的比较

#2

MySQL will do the fulltext search first, then look up the rest of the info, filter on price, sort on price, and finally deliver 30. There is essentially no way to shorten that process.

MySQL将首先进行全文搜索，然后查找其余信息，过滤价格，排序价格，最后交付30个。根本没有办法缩短这个过程。

Yes, caching is likely to be the explanation for 30 seconds becoming 1 second.

是的，缓存很可能是30秒变成1秒的原因。

Switching to InnoDB (which now has FULLTEXT) may provide some benefits.

切换到InnoDB(现在已经有了全文)可能会带来一些好处。

If running entirely MyISAM, do you have key_buffer_size set to about 20% of available RAM? If you were much lower (or higher) than this, that could cause performance problems.

如果完全运行isam，是否将key_buffer_size设置为可用RAM的20% ?如果您的级别比这个低(或高)得多，则可能会导致性能问题。

If running entirely InnoDB, set innodb_buffer_pool_size to about 70% of available RAM.

如果完全运行InnoDB，则将innodb_buffer_pool_size设置为可用RAM的70%左右。

#1