没有结果时，查询工作太慢.如何改善呢? [英] Query works too slow when there is no results. How to improve it?

问题描述

我有三个桌子

filters (id, name)
items(item_id, name)
items_filters(item_id, filter_id, value_id)
values(id, filter_id, filter_value)

项目中约有20000个条目. 在item_filters中大约有80000个条目.

about 20000 entries in items. about 80000 entries in items_filters.

SELECT i.*
    FROM items_filters itf INNER JOIN items i ON i.item_id = itf.item_id
     WHERE (itf.filter_id = 1 AND itf.value_id = '1') 
     OR (itf.filter_id = 2 AND itf.value_id = '7') 
    GROUP BY itf.item_id 
    WITH ROLLUP 
    HAVING COUNT(*) = 2
   LIMIT 0,10;

存在与查询匹配的项的时间为0.008，没有匹配项的时间为0.05.

It 0.008 time when there is entries that match query and 0.05 when no entries match.

我之前尝试过不同的版本:

I tried different variations before:

SELECT * FROM items WHERE item_id IN (
    SELECT `item_id` 
     FROM `items_filters` 
     WHERE (`filter_id`='1' AND `value_id`=1) 
     OR (`filter_id`='2' AND `value_id`=7)
    GROUP BY `item_id` 
    HAVING COUNT(*) = 2
   ) LIMIT 0,6;

当没有条目时，这将完全冻结mysql.

This completely freezes mysql when there are no entries.

我真的不明白的是 选择i.* FROM items_filters itf INNER JOIN项目i ON i.item_id = itf.item_id 其中itf.filter_id = 1和itf.value_id ='1'限制为0,1 如果找不到条目，​​则需要〜0.05，如果没有条目，则需要〜0.008

What I really don't get is that SELECT i.* FROM items_filters itf INNER JOIN items i ON i.item_id = itf.item_id WHERE itf.filter_id = 1 AND itf.value_id = '1' LIMIT 0,1 takes ~0.05 when no entries found and ~0.008 when there are

说明

| id | select_type | table | type | possible_keys | key     | key_len | ref                 | rows | Extra                           |
|  1 | SIMPLE      | i     | ALL  | PRIMARY       | NULL    | NULL    | NULL                |   10 | Using temporary; Using filesort |
|  1 | SIMPLE      | itf   | ref  | item_id       | item_id | 4       | ss_stylet.i.item_id |    1 | Using where; Using index        |

推荐答案

除了确保对两个(filter_id，value_id)的items_filters进行索引并对其进行索引外，我还要预先用一个group by来对您的商品ID进行资格预审，然后再加入这些商品桌子.看来您正在尝试寻找符合两个特定条件的物品，对于这些物品，请抓住这些物品...

Aside from ensuring and index on items_filters on both (filter_id, value_id), I would prequalify your item IDs up front with a group by, THEN join to the items table. It looks like you are trying to find an item that meets two specific conditions, and for those, grab the items...

即使内部查询返回的每个ID仅有一个实例，我也将外部的分组汇总"保留在外部.但是，由于内部查询已经应用了0,10条记录的限制，因此它不会抛出太多要连接到您的项目表的结果.

I've also left the "group by with rollup" in the outer, even though there will be a single instance per ID returned from the inner query. But since the inner query is already applying the limit of 0,10 records, its not throwing too many results to be joined to your items table.

但是，由于您没有进行任何汇总，因此我相信外部汇总依据和汇总不会真正为您提供任何好处，否则可能会被删除.

However, since you are not doing any aggregates, I believe the outer group by and rollup are not really going to provide you any benefit and could otherwise be removed.

SELECT i.*
   FROM
      ( select itf.item_id
           from items_filters itf
          WHERE (itf.filter_id = 1 AND itf.value_id = '1') 
             OR (itf.filter_id = 2 AND itf.value_id = '7') 
          GROUP BY itf.item_id 
          HAVING COUNT(*) = 2
          LIMIT 0, 10 ) PreQualified
      JOIN items i 
         ON  PreQualified.item_id = i.item_id

另一种方法可能是对内部查询进行JOIN操作，因此您甚至不需要按and来应用组.由于您明确要查找两个项目，因此我将尝试以下操作.这样，第一个限定词是它必须具有ID = 1和value ='1'的条目.它甚至没有命中那个条目，它永远不会关心第二个.然后，通过将联接应用于相同的表(别名为itf2)，它必须在相同的ID上找到AND-第二个条件(id = 2值='7').从根本上来说，这看起来像是在第一通行证上首先看起来像是一次通行证，而在考虑其他任何事情之前，最重要的是.在获得商品详细信息之前，这仍然会导致您的数量限制为10.

Another approach MIGHT be to do a JOIN on the inner query so you don't even need to apply a group by and having. Since you are explicitly looking for exactly two items, I would then try the following. This way, the first qualifier is it MUST have an entry of the ID = 1 and value = '1'. It it doesn't even hit THAT entry, it would never CARE about the second. Then, by applying a join to the same table (aliased itf2), it has to find on that same ID -- AND the conditions for the second (id = 2 value = '7'). This basically forces a look almost like a single pass against the one entry FIRST and foremost before CONSIDERING anything else. That would STILL result in your limited set of 10 before getting item details.

SELECT i.*
   FROM
      ( select itf.item_id
           from items_filters itf
              join items_filters itf2
                 on itf.item_id = itf2.item_id
                AND itf2.filter_id = 2 
                AND itf2.value_id = '7'
          WHERE 
             itf.filter_id = 1 AND itf.value_id = '1'
          LIMIT 0, 10 ) PreQualified
      JOIN items i 
         ON  PreQualified.item_id = i.item_id

根据您对重复项的评论(这也是我的预期)，我还通过/汇总删除了该组.

I also removed the group by / with rollup as per your comment of duplicates (which is what I expected).

这篇关于没有结果时，查询工作太慢.如何改善呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！