Django: efficient database search

Question

I need an efficient way to search through my models to find a specific User, here's a list,

User - list of users, their names, etc.
Events - table of events for all users, on when they're not available
Skills - many-to-many relationship with the User, a User could have a lot of skills
Contracts - many-to-one with User, a User could work on multiple contracts, each with a rating (if completed)
... etc.

So I got a lot of tables linked to the User table. I need to search for a set of users fitting certain criteria; for example, he's available from next Thurs through Fri, has x/y/z skills, and has received an average 4 rating on all his completed contracts.

Is there some way to do this search efficiently while minimizing the # of times I hit the database? Sorry if this is a very newb question.

Thanks!

The only option I see is denormalization. Try, benchmark, choose the the most efficient. — DrTyrsa
– DrTyrsa, Commented Dec 14, 2011 at 12:01
So this is a trade-off between read speed and storage (also write speed) right? I think what I want to try is a 2-step search that hits the database twice but reduces the search set; first search for availability, then only search within those that are available, the other skill / rating / etc. criteria. — reedvoid
– reedvoid, Commented Dec 14, 2011 at 12:37
This isn't a theoretical problem, you need benchmarking. Sometimes several separate queries are faster than a complex one, sometimes not. DB hit number by itself shows nothing, 3 indexed lookups can be faster than 1 heavy query with joins and subqueries. — DrTyrsa
– DrTyrsa, Commented Dec 14, 2011 at 12:54
We will find it easier to comment on how to create a minimal set of queries if we can see your models, and what you have now to do this. Note that you can filter by queries which span relationships, which will definitely help reduce the number of database hits. — Marcin
– Marcin, Commented Dec 14, 2011 at 13:57
I'm in the process of writing the code, once it's done I'll post it here. Thanks for all the comments! — reedvoid
– reedvoid, Commented Dec 14, 2011 at 17:28

miki725 · Accepted Answer · 2011-12-14 13:53:34Z

1

Not sure if this method will solve you issue for all 4 cases, but at least it should help you out in the first one - querying users data efficiently.

I usually find using values or values_list query function faster because it slims down the SELECT part of the actual SQL, and therefore you will get results faster. Django docs regarding this.

Also worth mentioning that starting with new dev version within values and values_list you can query any type of relationship, including many_to_one.

And finally you might find in_bulk also useful. If I do a complex query, you might try to query the ids first of some models using values or values_list and then use in_bulk to get the model instances faster. Django docs about that.

answered Dec 14, 2011 at 13:53

miki725

27.9k17 gold badges109 silver badges122 bronze badges

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

Collectives™ on Stack Overflow

Django: efficient database search

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related