1

I need to get last n records from m tables (lets say m=n=~10 for now) ordered by date (also supporting offset would be nice). This should show user his last activity. These tables will contain mostly hundreds or thousands of records for that user. How can I do that most efficient way? I'm using Doctrine 2 and these tables has no relation to each other.

I though about some solutions not sure whats the best approach:

  1. Create separate table and put records there. If any change happen (if user do any change inside the system that should be shown inside log table) it will be inserted in this table. This should be pretty fast, but it will be hard to manage and I don't want to use this approach yet.
  2. Get last n records from every table and then sort them (out of DB) and limit to n. This seems to be pretty straightforward but with more tables there will be quite high overhead. For 10 tables 90% of records will be thrown away. If offset is used, it would be even worse. Also this mean m queries.
  3. Create single native query and get id and type of last n items doing union of all tables. Like SELECT id, date, type FROM (SELECT a.id, a.date, 'a' AS type FROM a ORDER BY a.date DESC LIMIT 10 UNION b.id, b.date, 'b' AS type ORDER BY b.date DESC LIMIT 10) ORDER BY date DESC LIMIT 10. Then create at most m queries getting these entities. This should be a bit better than 2., but requires native query.

Is there any other way how to get these records?

Thank you

Daniel Robenek
  • 13
  • 3
  • 1
    Why are there m tables, instead of, say, 1? – Strawberry Jul 17 '16 at 08:58
  • Well lets say there is a table for purchases and discussions. I want to show 1st record as `You did purchase at 3pm`, 2nd `You did comment at 2pm`, 3rd `You did comment at 1pm` with some details – Daniel Robenek Jul 17 '16 at 09:00

1 Answers1

0
  1. is not hard to manage, it just is an additional insert for each insert you are doing for the "action"-tables.

You could also solve this by using a trigger I'd guess, so you wouldn't even have to implement it in the application code. https://stackoverflow.com/a/4754333/3595565

  1. Wouldn't it be "get last n records by a specific user from each of those tables? So don't see a lot of problems with this approach, though I also think it is the least ideal way to handle things.

  2. Would be like the 2nd option, but the database handles the sorting which makes this approach a lot more viable.

Conclusion: (opinion based)

You should choose between options 1 and 3. The main questions should be

  • is it ok to store redundant data

  • is it ok to have logic outside of your application and inside of your database (trigger)

Using the logging table would make things pretty straight forward. But you will duplicate data.

If you are ok with using a trigger to fill the logging table, things will be more simple, but it has it's downside as it requires additional documentation etc. so nobody wonders "where is that data coming from?"

Community
  • 1
  • 1
Philipp
  • 2,787
  • 2
  • 25
  • 27