0

I started reviewing the performed SQL queries so I can improve them (caching, reformulating them, etc) in my Django project.

For example, to get a single object from the database, this:

Company.objects.filter(pk=1)[:1]

Is faster than:

Company.objects.filter(pk=1)

Because in the former we are limiting the number of lookups. This is obvious, but what is not too obvious is how to set a limit when accessing a property of a model that happens to be a ForeignKey. For example, if CompanyModel is a model that has a OneToOneField to Company, and we try to access some property from the main model:

test = Company.objects.filter(pk=1)[:1]
profile = test.CompanyProfile.owner

The query that will be executed to get information about the CompanyProfile will not have any kind of limit, thus will traverse the whole table to find as many records as possible. How can I set a limit so that it does not do that?

user125498
  • 25
  • 4
  • I'm not sure what database you're using but is it really faster to add LIMIT 1 to the end of an sql query which is using the primary key and will only ever match one row? – robert_b_clarke Dec 19 '14 at 08:44
  • I am using pgSQL and I would say it depends of the size of the table. If you have a table with one million entries and you just want to get one, I assume it is better to get that one entry and stop looking for more as it would be resource-consuming and useless. – user125498 Dec 19 '14 at 08:46

1 Answers1

0

Your "optimisation" isn't obvious, and I'd be incredibly skeptical that it's even an optimisation at all.

Databases are in general really good at optimising their queries, especially in simple cases like this. If you ask the database for a record via its primary key, it doesn't scan through the whole database: instead, it uses the index for that column. That index is in order; so as soon as the db encounters a non-matching record, which it will immediately, it will stop scanning. There is absolutely no need to limit that query.

Exactly the same is true of your foreign key lookup. The FK points to the primary key of the owner table; so once again the database only needs to scan as far as the first non-matching key.

Daniel Roseman
  • 588,541
  • 66
  • 880
  • 895