MySQL - using the results of one query for use in another query

Question

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

SELECT photoID FROM photoSearch WHERE photoID BETWEEN 1 AND 50000; 

I was about to run this request with the photo id just returned.

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

But I understand from others and resources that the IN clause will not work well, especially since I can have 100,000 plus photo IDs.

Is it good to store the photo ID from the top query in another table (resultsTbl) or in a very long row? If so, either am I using a join or subselect to request these IDs (in the bottom query) instead of using IN? Or ... is there any other way that will do the job considering performance?

Any help on this would be greatly appreciated.

Answer 1

• Is it good to store the photo ID from the top query in another table (resultsTbl) or in a very long row?

  • In another table: Generally not. If there are many identifiers, and you execute the top query in other places, then storing it in the cache table may be in order. Although for this case, the "top query" is likely to remain in memory, so you should probably use a subquery.

  • In a very long line: None. String operations are usually very intense.

• If so, either am I using a join or subselect to request these IDs (in the bottom query) instead of using IN?

  • Instead of storing it in a temporary table, just do a JOIN to get started (see example below). In some cases, the databases will join 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