0

This is a bit of a 'best practice' question...

  • I have a USERS table (user_id, name)
  • I have a FRIENDS table (friend_id, name)
  • I have a FRIEND_FACTS table (friend_id, user_id, fact_id, fact_content)

What I want to do is 'get latest friend fact for each friend' -> for a particular user.

The challenge I have is 'get latest' - my question is about the best way to do this, and to make it performant. These are my thoughts on approach:

  1. Add a timestamp to each FRIEND_FACT entry, and query the table for each friend and return ORDER BY timestamp LIMIT 1. (This seems viable, but will it create a lot of repetition of sorting if querying 1000s of friends at once?)
  2. Each time a fact is added, replace an entry in a LATEST_FACTS relational table that simply stores the fact_id and the friend_id. (This seems cleaner, but means maintaining another table )

Any other ideas? I'm not a wizard with these things so am trying to get the most performant method without making the solution too convoluted.

lucastobrazil
  • 93
  • 9
  • I know this was closed, but the second part of the question is different - do I therefore need to add a timestamp column in order to determine which is the latest entry? – lucastobrazil Jan 28 '16 at 08:51

1 Answers1

0

Something I might do is: $query = "select max(fact_id) from fact where friend_id = '$friendId'";

you can then grab all the information for the face with max id? is this what you were trying to achieve?

Sam Orozco
  • 1,258
  • 1
  • 13
  • 27