仅在父查询的结果上运行子查询

I am running a search query for a site that collects element that match the query. However, there may or may not be one or more items that are linked to the element. So, I have a subquery that counts the matches of all of the items linked to an element.

我正在为收集与查询匹配的元素的网站运行搜索查询。但是,可能有也可能没有一个或多个链接到该元素的项目。所以,我有一个子查询,用于计算链接到元素的所有项的匹配。

This query does work. But incredibly slowly. It takes me around 50 seconds at the moment. If I ditch the subquery it is much, much faster.

此查询确实有效。但令人难以置信的慢。现在我需要大约50秒。如果我放弃子查询,它会更快,更快。

SELECT DISTINCT e.id, 
    MATCH (e.heading) AGAINST ('+(room)') AS h_score, 
    MATCH (e.text) AGAINST ('+(room)') AS t_score, 
    ( 
        SELECT SUM( MATCH (item.heading, item.text) AGAINST ('+(room)') ) AS itemscore 
             FROM item LEFT JOIN _element_item ON item.id = _element_item.item_id 
             WHERE _element_item.item_id = e.id 
             AND MATCH (item.heading, item.text) AGAINST ('+(room)')
    ) AS i_score 

    FROM element AS e 
    LEFT JOIN _element_brand ON e.id = _element_brand.element_id 
    LEFT JOIN _element_language ON e.id = _element_language.element_id 
    LEFT JOIN _element_region ON e.id = _element_region.element_id 
    LEFT JOIN _element_type ON e.id = _element_type.element_id 
    LEFT JOIN _element_group ON e.id = _element_group.element_id     

    WHERE _element_brand.brand_id = 1 
    AND _element_language.language_iso = 'en' 
    AND _element_region.region_id = 1 
    AND _element_type.type_id = 1 
    AND _element_group.group_id = 1 
    AND e.replacement_id IS NULL 
    AND e.status = 1 
    AND MATCH (e.heading, e.text) AGAINST ('+(room)') 
    ORDER BY t_score + h_score DESC LIMIT 100

Is there a way to get this to run faster?

有没有办法让它跑得更快?

I'm guessing it is running the full subquery for every element before matching the element? Could I get it to only run the subquery on matches in the parent query? If so, how?

我猜它在匹配元素之前为每个元素运行完整的子查询?我可以让它只在父查询中的匹配项上运行子查询吗?如果是这样,怎么样?

1 个解决方案

#1

Let me try to clarify the left-join for your sample scenarios.

让我试着澄清你的示例场景的左连接。

select e.heading
   FROM element AS e
           LEFT JOIN _element_brand 
              ON e.id = _element_brand.element_id 
   WHERE 
          e.SomeColumn = 'test'
      AND _element_brand.brand_id = 1

is the same thing as

是一回事

select e.heading
   FROM element AS e
           JOIN _element_brand 
              ON e.id = _element_brand.element_id 
   WHERE 
          e.SomeColumn = 'test'
      AND _element_brand.brand_id = 1

because the WHERE clause is FORCING to only return records that DO have a matching _element_brand record AND that record's brand_id = 1. So this will return only records where the "e.SomeColumn = 'test'" AND the brand_id = 1.

因为WHERE子句FORCING只返回具有匹配的_element_brand记录且记录的brand_id = 1的记录。因此,这将只返回“e.SomeColumn ='test'”和brand_id = 1的记录。

Now, look at a true left-join

现在,看一个真正的左连接

select e.heading
   FROM element AS e
           LEFT JOIN _element_brand 
              ON e.id = _element_brand.element_id 
             AND _element_brand.brand_id = 1 
   WHERE 
      e.SomeColumn = 'test'

This will return all records from element that have "e.SomeColumn = 'test'" REGARDLESS of having an entry in the _element_Brand table and the _element_brand.brand_id = 1.

这将返回具有“e.SomeColumn ='test'”的元素的所有记录,而不管_element_Brand表中的条目和_element_brand.brand_id = 1。

So, if you had 100 elements with SomeColumn = 'test', and of those, 50 records had valid records in the _element_brand table for the corresponding ID. Of those 50, only 20 specificcally had a brand_id = 1...

因此,如果你有100个带有SomeColumn ='test'的元素,那么50个记录在_element_brand表中有相应ID的有效记录。在这50个中,只有20个具体品牌有brand_id = 1 ......

The first two sample queries (left-join with WHERE, and the join) would ONLY return the 20 records that specifically had the 20 records.

前两个示例查询(与WHERE的左连接和连接)将仅返回具有20条记录的20条记录。

The LAST query would still return the 100 records that just qualified based on 'test'.

最后一个查询仍将返回100个基于'test'限定的记录。

Hope this clarifies if based on your data scenario... sometimes seeing in a book or document might not actually clarify someone trying to grasp it.

希望这可以澄清是否基于您的数据场景...有时在书或文档中看到可能实际上并未澄清某人试图掌握它。

SELECT DISTINCT 
      STRAIGHT_JOIN
      e.id, 
      MATCH (e.heading) AGAINST ('+(room)') AS h_score, 
      MATCH (e.text) AGAINST ('+(room)') AS t_score, 
      ( SELECT SUM( MATCH (item.heading, item.text) AGAINST ('+(room)') ) 
           FROM _element_item
              JOIN item 
                 ON _element_item.item_id = item.id  
                AND MATCH (item.heading, item.text) AGAINST ('+(room)')
           WHERE 
              e.id = _element_item.item_id ) AS i_score 
   FROM 
      element AS e 
         JOIN _element_brand 
            ON e.id = _element_brand.element_id 
            AND _element_brand.brand_id = 1 

         JOIN _element_language 
            ON e.id = _element_language.element_id 
            AND _element_language.language_iso = 'en' 

         JOIN _element_region 
            ON e.id = _element_region.element_id 
           AND _element_region.region_id = 1 

         JOIN _element_type 
           ON e.id = _element_type.element_id 
          AND _element_type.type_id = 1 

         JOIN _element_group ON e.id = _element_group.element_id     
          AND _element_group.group_id = 1 
   WHERE 
          e.status = 1 
      and e.replacement_id IS NULL 
      AND MATCH (e.heading, e.text) AGAINST ('+(room)') 
   ORDER BY 
      t_score + h_score DESC 
   LIMIT 100

I've restructured to joins. In addition, slightly altered your field select sum() for i_score that appears to be killing your query. I also added keyword "STRAIGHT_JOIN" at the top to tell the query run in the order presented.

我已经重组了加入。此外,稍微更改了i_score的字段选择sum()似乎正在查杀您的查询。我还在顶部添加了关键字“STRAIGHT_JOIN”来告诉查询以所显示的顺序运行。

My only review question on the field select... You are joining

我在该领域的唯一评论问题选择...你正在加入

e.id to _element_item.item_id, then from _element_item.item_id to item.id....

e.id到_element_item.item_id,然后从_element_item.item_id到item.id ....

So, that said, it MIGHT be simplified that

所以,那说,可能会简化

e.id = item.id and you can completely remove the _element_item table if they truly are the same, but it appears to be a bridge table that for a given element could have many items.

e.id = item.id你可以完全删除_element_item表,如果它们真的相同,但它似乎是一个桥表,对于给定的元素可能有很多项。

#1