MySQL - 使用一个查询的结果在另一个查询中使用

MySQL - Using results from one query to use in another query

提问by TheCarver

Let's say I have this, that produces 50,000 rows:

假设我有这个，它产生 50,000 行：

SELECT photoID FROM photoSearch WHERE photoID BETWEEN 1 AND 50000;

I was going to run this query against those photoID's just returned.

我打算针对刚刚返回的那些 photoID 运行此查询。

SELECT COUNT(people) AS totalPeople, people
FROM people
INNER JOIN photopeople ON photoPeople.peopleID = people.PeopleID
WHERE photoid IN ('ID's from results')
GROUP BY people
ORDER BY totalPeople DESC

But I understand from others and resources, that the IN clause will not perform well, especially as I could have 100,000 plus photoID's.

但我从其他人和资源中了解到，IN 子句不会表现良好，尤其是因为我可能有 100,000 多个照片 ID。

Is it a good idea to store the photoID's from the top query in another table (resultsTbl) or in a very long string? If yes to either, do I use a join or a sub-select to query those ID's (in the bottom query), instead of using IN? Or... is there another way that will do the job keeping performance in mind?

将顶部查询中的 photoID 存储在另一个表 (resultsTbl) 或很长的字符串中是个好主意吗？如果是，我是否使用连接或子选择来查询这些 ID（在底部查询中），而不是使用 IN？或者……有没有另一种方法可以让工作记住性能？

Any help on this would be gratefully received.

对此的任何帮助将不胜感激。

回答by vol7ron

1. Is it a good idea to store the photoID's from the top query in another table (resultsTbl) or in a very long string?

   • In another table:Generally, no. If there are a lot of IDs and you perform the top query in other places, then storing it in a cache-ing table could be okay. Though, for this case, the "top query" is most likely going to remain in memory, so you should probably use a subselect.

   • In a very long string:No. String operations are generally highly CPU intensive.

2. If yes to either, do I use a join or a sub-select to query those ID's (in the bottom query), instead of using IN?

   • Instead of storing it in a temporary table, just do a JOIN to start with (see example below). In some cases databases will join an IN(select * from foo)for you.

1. 将顶部查询中的 photoID 存储在另一个表 (resultsTbl) 或很长的字符串中是个好主意吗？

   • 在另一个表格中：一般来说，没有。如果有很多 ID 并且您在其他地方执行 top 查询，那么将其存储在缓存表中可能没问题。但是，对于这种情况，“顶级查询”很可能会保留在内存中，因此您可能应该使用子选择。

   • 在很长的字符串中：否。字符串操作通常占用大量 CPU。

2. 如果是，我是否使用连接或子选择来查询这些 ID（在底部查询中），而不是使用 IN？

   • 不要将其存储在临时表中，只需执行 JOIN 即可开始（请参见下面的示例）。在某些情况下，数据库会IN(select * from foo)为您加入。

---

---

• Using IN(subselect):

  SELECT     count(people) AS totalPeople
           , people
  FROM       people
  INNER JOIN photopeople ON photoPeople.peopleID = people.PeopleID
  WHERE      photoid IN (select  photoID 
                         from    photoSearch 
                         where   photoID 
                         between 1 AND 50000)
  GROUP BY   people
  ORDER BY   totalPeople DESC
  

• Using JOIN

  SELECT     count(people) AS totalPeople
           , people
  FROM       people
  INNER JOIN photopeople ON photoPeople.peopleID = people.PeopleID
  INNER JOIN photoSearch ON photopeople.photoid  = photoSearch.photoID
  WHERE      photoID between 1 AND 50000
  GROUP BY   people
  ORDER BY   totalPeople DESC
  

• 使用 IN（子选择）：

  SELECT     count(people) AS totalPeople
           , people
  FROM       people
  INNER JOIN photopeople ON photoPeople.peopleID = people.PeopleID
  WHERE      photoid IN (select  photoID 
                         from    photoSearch 
                         where   photoID 
                         between 1 AND 50000)
  GROUP BY   people
  ORDER BY   totalPeople DESC
  

• 使用连接

  SELECT     count(people) AS totalPeople
           , people
  FROM       people
  INNER JOIN photopeople ON photoPeople.peopleID = people.PeopleID
  INNER JOIN photoSearch ON photopeople.photoid  = photoSearch.photoID
  WHERE      photoID between 1 AND 50000
  GROUP BY   people
  ORDER BY   totalPeople DESC