1

I'm not very knowledgeable about databases. I would want to retrieve, say the "newest" 10 rows with owner ID matching something, and then perhaps paginate to retrieve the next "newest" 10 rows with that owner, and so on. But say I'm adding more and more rows into a database table -- at some point, would such a query become unbearably slow, or are databases generally good enough that this won't be a worry?

I imagine it would be an issue because to get the "newest" 10 rows you'd have to order by date, which is O(n log n). With this assumption, I sought a possible solution from SQL Server SELECT LAST N Rows. It pointed me to http://www.sqlservercurry.com/2009/02/retrieve-last-n-rows-based-on-condition.html where I found that there is a PARTITION BY option for a query. I imagine this means first selecting all the rows that match the owner ID, and THEN ordering them, which would be significantly faster, and fast enough to not worry about for most applications. Is this the correct understanding?

Otherwise, is there some better way to get the "newest" N rows ( seems to suggest it is)?

I'm developing the app in Django if anyone knows a convenient way, but otherwise Django also allows raw database queries.

personjerry
  • 1,045
  • 8
  • 28

1 Answers1

1

Okay, if you are using django, then you don't have to worry about DB's complexity. ORM is here to resolve your worries.

Simple fact, Django uses lazy query. So, it will reduce your DB hits and improve system performance.

So, according to your initial part of question, you can simply run this query:

queryset = YourModel.objects.filter(**lookup_condition).order_by('id')

It will get a queryset with the objects which match the condition from database of that Model class. For details, check this: https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.query.QuerySet.filter

And to paginate over it, run like this:

 first_ten_values = queryset[0:9]
 second_ten_values = queryset[10:19]
 ...
ruddra
  • 50,746
  • 7
  • 78
  • 101
  • So, when I do `first_ten_values = queryset[0:9]` Django's QuerySet is smart enough "optimize" for me? – personjerry Jan 12 '16 at 06:44
  • yes. Kindly check this SO answer as well: http://stackoverflow.com/questions/6574003/django-limiting-query-results And this link: https://docs.djangoproject.com/en/dev/topics/db/queries/#limiting-querysets – ruddra Jan 12 '16 at 06:45
  • Thanks! Do you know if `Model.objects.filter(BLAH).order_by('id')` would perform differently than `Model.objects.order_by('id').filter(BLAH)`? – personjerry Jan 12 '16 at 06:57
  • 1
    No, they will perform same. – ruddra Jan 12 '16 at 07:19