如何简化查询并提高性能?

Have this query that I guess can be written in a more simple way that will perform better:

让我认为可以用一种更简单的方式编写这个查询，它的性能会更好:

SELECT COUNT(DISTINCT productid)
FROM productwords pw
WHERE productid IN
(SELECT productid FROM productwords pw JOIN words w 
  ON pw.wordid = w.id WHERE word = 'nike')
AND productid IN 
(SELECT productid FROM productwords pw JOIN words w 
  ON pw.wordid = w.id WHERE word = 'free')

Goal of the query is to get the number of distinct productids that have words = both 'nike' and 'free'.

查询的目的是获取具有单词= 'nike'和'free'的不同产品的数量。

Thanks!

谢谢!

3 个解决方案

#1

If I'm understanding your question correctly, this should be better just using a join:

如果我正确地理解了你的问题，这应该更好地使用一个连接:

SELECT COUNT(DISTINCT productid)
FROM productwords pw
   JOIN words w ON pw.productid = w.id 
WHERE word IN ('nike','free')

Your current query on pw = w.id isn't actually correct. Whichever field you are attempting to join on should be included above. I guessed productid, but perhaps its wordid instead...

您对pw = w的当前查询。id并不是正确的。您试图加入的任何字段都应该包含在上面。我猜是有产品的，但也许它更糟……

#2

Very strange that the foreign key column in the productswords (product swords?) table is named pw, and not word_id. (But it's just a column name, we can name it whatever we want. If the foreign key is actually named something else, then replace pw.pw in the queries below with pw.column_name ...)

非常奇怪的是，productswords (product sword ?)表中的外键列被命名为pw，而不是word_id。(但它只是一个列名，我们可以任意命名它。如果外键实际上被命名为其他名称，那么替换pw。pw在下面的查询中使用pw。column_name…)

To find products that match both of the two words, meaning, only return a product if it matches the word 'nike' AND if it matches the word 'free'...

要找到符合这两个词的产品，意思是，只有当它与“nike”匹配，如果它与“free”匹配，才返回产品……

We can use a join operation, combined with a GROUP BY and a HAVING clause...

我们可以使用join操作，结合GROUP BY和have子句…

 SELECT pw.productid 
   FROM productwords pw
   JOIN words w 
     ON w.id = pw.pw
    AND w.word IN ('nike','free')   -- keywords to match
  GROUP BY pw.productid
 HAVING COUNT(DISTINCT w.word) = 2  -- number of keywords to match

To get just a count, we can wrap that in query in parens and reference it as an inline view...

为了得到一个计数，我们可以在查询中把它包装成参数，并将它作为内联视图引用……

 SELECT COUNT(1)
   FROM ( SELECT pw.productid 
            FROM productwords pw
            JOIN words w 
              ON w.id = pw.pw
             AND w.word IN ('nike','free')   -- keywords you are looking for
           GROUP BY pw.productid
          HAVING COUNT(DISTINCT w.word) = 2  -- number of keywords to match
        ) v

There are other query patterns that will return an equivalent result.

还有其他查询模式将返回等效的结果。

For example, using an EXISTS (correlated subquery) pattern to get only the products that match BOTH of the specified words...

例如，使用exist(关联子查询)模式只获取匹配两个指定单词的产品……

 SELECT pw.productid
   FROM productwords pw
  WHERE EXISTS ( SELECT 1
                   FROM words w
                  WHERE w.id = pw.pw      -- related to row on outer query
                    AND w.word IN ('nike','free')
               )
 GROUP BY pw.productid
HAVING COUNT(DISTINCT pw.pw) = 2

To get just a count, again, wrap that in parens and reference it as an inline view.

为了得到一个计数，再次，将它包装在括号中，并将其作为内联视图引用。

For non-trivial sets, having suitable indexes available can significantly improve performance of both queries. For example

对于非平凡集，拥有合适的索引可以显著提高两个查询的性能。例如

   ... ON productswords (pw, productid)
   ... ON words (word, id)

#3

you can simply the query by doing like this

您可以这样简单地执行查询

select count(*), w.word from product p left join words w on w.id = p.word_id where w.name = 'nike' or w.name = 'free' group by w.name

hope this query answer your question. you could also filter by

希望这个问题能回答你的问题。你也可以过滤。

where w.name in ('nike', 'free')

if you have many rows (let's say 10,000++) you could improve like this

如果有很多行(比如10,000++)，可以这样改进

SELECT count(*), w.name from product p JOIN (select id, name from words where name = 'nike' or name = 'free') w on w.id = p.word_id group by w.name

#1