Let us say I've this, that creates 50,000 rows:

SELECT photoID FROM photoSearch WHERE photoID BETWEEN 1 AND 50000;

I would run this question against individuals photoID's just came back.

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

However I understand from others and assets, the IN clause won't succeed, especially when i might have 100,000 plus photoID's.

Could it be smart to keep photoID's in the top query in another table (resultsTbl) or in an exceedingly lengthy string? If so either to, will i make use of a join or perhaps a sub-choose to question individuals ID's (towards the bottom query), rather than using IN? Or... can there be one other way that is going to do the task keeping performance in your mind?

Any help on this is gratefully received.

  1. Could it be smart to keep photoID's in the top query in another table (resultsTbl) or in an exceedingly lengthy string?

    • In another table: Generally, no. If you will find lots of IDs and also you carry out the top query elsewhere, then storing it inside a cache-ing table might be okay. Though, with this situation, the "top query" is probably likely to stay in memory, which means you should most likely make use of a subselect.

    • In an exceedingly lengthy string: No. String procedures are usually highly CPU intensive.

  2. If so either to, will i make use of a join or perhaps a sub-choose to question individuals ID's (towards the bottom query), rather than using IN?

    • Rather than storing it inside a temporary table, just perform a JOIN to begin with (see example below). In some instances databases will join an IN(select * from foo) for you personally.

  • 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