为什么WHERE IN会非常慢

I've been using the following query for some time, but as the results database has grown, it has become slow to the point of being un usable. Could anyone suggest an alternative way of doing this?

我已经使用了以下查询一段时间了,但随着结果数据库的增长,它已变得缓慢到无法使用的程度。有人可以提出另一种方法吗?

I've got fulltext indices on c.name and r.scope

我在c.name和r.scope上有全文索引

SELECT * FROM results r 
        INNER JOIN categories c on r.id = c.result_id 
        INNER JOIN tags t on r.id = t.result_id 
        WHERE c.name in ('purchase', 'single_family', 'other')

        AND ( r.scope = 'all' OR r.scope = 'hi' )
        AND published = 1
    GROUP BY r.id
        HAVING COUNT(c.c_id) >= 3
        ORDER BY r.usefulness DESC
        LIMIT 8 OFFSET 0

Any help would be much appreciated. Thanks!

任何帮助将非常感激。谢谢!

Edit: Here's the result of EXPLAIN

编辑:这是EXPLAIN的结果

  id    select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  c   range   nameindex,name  nameindex   767     NULL    10036   Using where; Using temporary; Using filesort
1   SIMPLE  t   ALL     NULL    NULL    NULL    NULL    10229   Using where; Using join buffer
1   SIMPLE  r   eq_ref  PRIMARY,scope   PRIMARY     4   rfw.t.result_id     1   Using where

4 个解决方案

#1

Update: query to create index

更新:查询以创建索引

CREATE INDEX name ON categories (name);

CREATE INDEX名称ON类别(名称);

Use Explain statement, the output of explain will help you to decide which column require index & other useful information.

使用Explain语句,explain的输出将帮助您确定哪个列需要索引和其他有用信息。

As specified in mysql doc:

如mysql doc中所指定:

When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order.

当您在SELECT语句之前使用关键字EXPLAIN时,MySQL会显示优化程序中有关查询执行计划的信息。也就是说,MySQL解释了它将如何处理语句,包括有关表如何连接以及以何种顺序连接的信息。

Run this:

    EXPLAIN SELECT * FROM results r 
    INNER JOIN categories c on r.id = c.result_id 
    INNER JOIN tags t on r.id = t.result_id 
    WHERE c.name in ('purchase', 'single_family', 'other')

    AND ( r.scope = 'all' OR r.scope = 'hi' )
    AND published = 1
GROUP BY r.id
    HAVING COUNT(c.c_id) >= 3
    ORDER BY r.usefulness DESC
    LIMIT 8 OFFSET 0

#2

You don't need a fulltext index here; a regular index is what mySQL will want to use for this field.

你这里不需要全文索引;常规索引是mySQL将要用于此字段的内容。

A fulltext index is intended for use with fulltext searching (ie the MATCH .. AGAINST query); it won't be relevant to a simple equals or IN () query.

全文索引旨在用于全文搜索(即MATCH .. AGAINST查询);它与简单的equals或IN()查询无关。

#3

When you want to make your query faster, you could do 3 things:

如果您想更快地进行查询,可以执行以下三项操作:

add index on c.name (slow query suggests that you're not having any)

在c.name上添加索引(慢查询表明你没有)

add count to categories (with index on it), which would contain cached data
WHERE c.count >= 3 will fetch only limited number of results
HAVING COUNT() >= 3 has to fetch all results before returning

将计数添加到类别(带有索引),其中包含缓存数据WHERE c.count> = 3将仅获取有限数量的结果HAVING COUNT()> = 3必须在返回之前获取所有结果

use WHERE c.id IN (...) instead of WHERE c.name in ('...) if possible...
id = identification = PRIMARY KEY, this is what it was meant to do

如果可能的话,使用WHERE c.id IN(...)代替WHERE c.name in('...)... id = identification = PRIMARY KEY,这就是它的意思

#4

-1

1) Select only required for your answer columns. 2) Setup propertly tables indexes. Also you can get some info using EXPLAIN SELECT ...

1)仅选择答案列所需。 2)设置属性表索引。您也可以使用EXPLAIN SELECT获取一些信息...

#1