MySQL/ASP - Confusing SQL Query

Question

I have a celebrity photo website, 100,000 photos and growing. The user has made a search "Oscar Awards Ceremony" and I currently loop through the results to display the relevant photos.

Now, I'd like to offer a search refinement column, that sits beside the results, that will show specific names of the people that were photographed at that particular event (based on photoID's from results), showing the quantity of photos next each name and displaying the names in order of highest quantity.

Like so:

You searched for: "Oscar Awards Ceremony" (12,489)

Specific People:

Brad Pitt (1,726)

Angelina Jolie (1,302)

Edward Norton (929)...

This is my database schema:

photoSearch (tbl)
photoID             INT(11)         Index       Auto-Increment
headline            VarChar(1000)   FullText
caption             VarChar(2500)   FullText
dateCreated         DateTime        Index

photoPeople (tbl)
photoID             INT(11)
peopleID            INT(11)

people (tbl)
peopleID            INT(11)         Primary     Auto-Increment
people              VarChar(255)    Index

So basically, I have a result set of photoID's from the users last search. Using that result set, I need to query the 'photoPeople' and 'people' table, to list the people that are related to those photoID's, I need to show the quantity and to order them by quantity.

How can I go about doing this? Can it be done in one query? Will I have to write the results in to a table to be able to count and order them?

This is what I have done so far but only lists peoples names:

SELECT * FROM people
INNER JOIN photopeople ON photoPeople.peopleID = people.PeopleID
WHERE photoID IN ('87345','8900','83458','63746')

I'm not looking for exact code, although it would be very helpful, I'm just looking for advice on how to accomplish this correctly with performance in-mind. I think my brain is capable of showing the relevant people, but not in any order and certainly without a count!

Any help gratefully received...

P.S. When I get a working solution together, I will apply this to my events table and keywords too. This means if somebody searched "Brad Pitt", the event "Oscar Awards Ceremony" will appear in the 'refine search' column, with 1,726 next to the event.

mrcrowl · Accepted Answer · 2011-09-15 22:41:45Z

2

IF I understand your schema correctly, this should work:

SELECT people.People AS Name, count(*) AS NumberOfPhotos 
FROM people
INNER JOIN photoPeople ON photoPeople.peopleID = people.PeopleID
WHERE photoPeople.photoID IN ('87345','8900','83458','63746')
GROUP BY photoPeople.PeopleID, people.People
ORDER BY NumberOfPhotos DESC

(Sorry, I'm more familiar with MSSQL, but I assume MySql is very similar)

edited Sep 15, 2011 at 22:41

answered Sep 15, 2011 at 22:34

mrcrowl

1,53416 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

TheCarver Over a year ago

Thanks. Funny that, I was just experimenting with an idea I had too and both are very similar, mine works and so does yours: SELECT COUNT(people) AS totalPeople, people FROM people INNER JOIN photopeople ON photoPeople.peopleID = people.PeopleID WHERE photoid IN ('87345','8900','83458','63746','82039','53696','53697','53698') GROUP BY people ORDER BY totalPeople DESC

TheCarver Over a year ago

Plus I'm only showing the top 5 names at that event with counts, so performance will be okay with this I think...

mrcrowl Over a year ago

Yeah, no worries. One further comment.. if you already have stored the query results (i.e. the photo IDs) in another table, then better to JOIN to that table, rather than using an IN statement.

TheCarver Over a year ago

I haven't stored the results anywhere. I select the photoID, headline, caption and dateCreated, as that is what's displayed with the photos and doing a 'do while not loop' I display the photos. Do you suggest storing the photoID's?

TheCarver Over a year ago

Or is using IN painfully slow?

|

Zak · Accepted Answer · 2011-09-15 22:19:50Z

0

This is outside of the sql query, but look into solr and faceting... you can do a search, then refine by facets. You also get neat functions like autocomplete, spell check, and stemming.

answered Sep 15, 2011 at 22:19

Zak

25.3k11 gold badges42 silver badges68 bronze badges

Collectives™ on Stack Overflow

MySQL/ASP - Confusing SQL Query

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related