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

Question

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

SELECT photoID FROM photoSearch WHERE photoID BETWEEN 1 AND 50000;

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

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.

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?

Any help on this would be gratefully received.

Answer 1

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.

---

• 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